CODEWITHSUNDEEP
excelvba
David Brossard
David Brossard

Reputation: 13832

Determine the last visible row in Microsoft Excel using VBA

I have an Excel sheet with a fixed number of visible rows. I want to write a macro to insert a row after the last visible row.

To do so I first want to determine what the current last visible row is. I tried to write the following but I get a compilation error. I don't know VB very well so I'm properly doing something wrong in the While loop.

Thanks.

Sub AddRequirementRule()
Dim rowNumber As Long
rowNumber = 1
While (Not ActiveSheet.Row(rowNumber).Hidden)
   rowNumber = rowNumber + 1
End While


MsgBox (rowNumber)

On a side note, where can I find more information about the error in Excel's Macro editor (VBA 7.0)?

Upvotes: 0

Views: 5363

Answers (2)

Trace
Trace

Reputation: 18859

I think that it won't work that way.
Try to check this way to avoid the compile error: 

While (Not thisworkbook.Sheets(1).range("A" & rowNumber).entirerow.hidden)
   rowNumber = rowNumber + 1
Wend

For more info on errors, I suggest that you use error handling. http://www.cpearson.com/excel/errorhandling.htm

Upvotes: 1

David Brossard
David Brossard

Reputation: 13832

My script is simply wrong. I shouldn't use End While but rather Wend.

I also had to do what Kim recommended: Range("A" & rowNumber).EntireRow.Hidden instead of Row(rowNumber).Hidden.

Here's the correct code:

Sub AddRequirementRule()
Dim rowNumber As Long
rowNumber = 1
While (Not ActiveSheet.Range("A" & rowNumber).EntireRow.Hidden)
   rowNumber = rowNumber + 1
Wend


MsgBox (rowNumber)


End Sub

Thanks!

Upvotes: 0

Related Questions