Hide/Unhide Rows

G

Guest

Hello
I am using the following code to hide/unhide rows:
Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Dim I As Long
Dim Hidden As Boolean
' For i = 14 To Cells(Rows.Count, "A").End(xlUp).Row
For I = 14 To 83
If Rows(I).EntireRow.Hidden Then
Hidden = True
Rows(I).EntireRow.Hidden = False
End If
Next I
If Hidden Then Exit Sub
' For i = 14 To Cells(Rows.Count, "A").End(xlUp).Row
For I = 14 To 83
If Cells(I, 1).Value <> "BA" Then
' Range(Cells(I, 1), Cells(I + 9, 1)).EntireRow.Hidden = True
Rows(I).Hidden = True
End If
Next I
End Sub

This works fine up to 83 rows. I am trying to modify to use the last row
istead of hard coded row number. My attempts to modify (which arent working)
have been commented out. What am I doing wrong?
Thanks
 
G

Guest

Al,
From testing your code, it appears that Cells(Rows.Count,
"A").End(xlUp).Row counts the visible rows so if you have hidden rows 14
onwards, Cells(Rows.Count, "A").End(xlUp).Row is set to 13 on next run and
hence no execution of the loops is done.
 
G

Guest

So how would I fix this?
Thanks!

Toppers said:
Al,
From testing your code, it appears that Cells(Rows.Count,
"A").End(xlUp).Row counts the visible rows so if you have hidden rows 14
onwards, Cells(Rows.Count, "A").End(xlUp).Row is set to 13 on next run and
hence no execution of the loops is done.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top