CODEWITHSUNDEEP
vbaexcelrange
user2131465
user2131465

Reputation: 97

Getting method range of object _worksheet failed for the below code

My code

Set myRange1 = ws1.Range("B5", ws1.Range("B5" & Rows.Count).End(xlUp))

Upvotes: 0

Views: 100

Answers (2)

T.Nakabayashi
T.Nakabayashi

Reputation: 1

I think the following part is strange.

ws1.Range("B5" & Rows.Count).End(xlUp)

"B5" & Rows.Count is not correct.

If you wont use Rows.Count. Try this.

ws1.Range("B" & ws1.Rows.Count).End(xlUp).Address

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

If you are trying to get the range from Cell "B5" untill the last row with data in Column B (with skipping blank cells in the middle) use the code below :

With ws1
    Set myRange1 = .Range("B5:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
End With

Option 2: If you are trying to get the range from Cell "B5" untill the last row with data in Column B (without skipping blank cells in the middle) use this optional code below :

With ws1
    Set myRange1 = .Range("B5:B" & .Range("B5").End(xlDown).Row)
End With

Upvotes: 1

Related Questions