CODEWITHSUNDEEP
vbaexcel
user1996971
user1996971

Reputation: 543

Better way to open VBA While loops than I have here?

Whenever I run a While.... Wend loop, I use a script based around this general idea.

Sub Macro1
Dim Rng as Range
Dim i as Long
i = 2
While i <= 10000
Set Rng = ("A" & i)

If Rng = Rng.Offset(0,1) Then
i = i + 1
ElseIf.....

EndIf 
Wend
End Sub

I didn't elaborate on portions of the script not relevant to my question. Anyway, what I'm asking about is the line `While i <=10000. Of course, this works well with the majority of tasks based around columns smaller than 10,000 rows in length. However, I understand that this isn't well written. If I want to write a script that will be applicable to all worksheets, whether column A contains 10 or 100000 rows, how can I adjust this opening to reflect that? Something like:

Dim X as long
Set X to the number of rows in column A
While i <= X

Would something like this work? Thanks in advance.

Upvotes: 0

Views: 44

Answers (2)

Zaider
Zaider

Reputation: 2013

You get the last row of a sheet:

Dim LastRow as Long
LastRow = Activesheet.Cells(Activesheet.Rows.Count, 1).End(-4162).Row

The -4162 is the value of the constant xlUP that is not available when doing automation from another program.

What this will do is go to the bottom of the specified column (the second value in .Cells) and then go up to the last row with a value, similar to pressing Ctrl+Up

and then use 

While i <= LastRow

Upvotes: 1

leowyn
leowyn

Reputation: 128

There's a bunch of ways you can find the last row, but most make use of End():

You can:

LastRow = Range("A1").End(xlDown).Row

or

LastRow = Range("B10000").End(xlUp).Row

or as the previous answer, which uses ActiveSheet.Rows.Count. The number after the comma in that one is the column number, so:

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 3).End(xlUp).Row

would use column C to do the End operation.

P.S. The "-4162" is just the numerical form of the "xlUp" constant.

Upvotes: 2

Related Questions