Hide/Unhide Rows
 

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

Hide/Unhide Rows
 

Al,

Have you tried using a constant for the hard coded 83?

See the link below for more information. I just read it myself, and I
am no expert, but I thought this might be helpful to you.

http://www.fmsinc.com/tpapers/vbacode/

Regards,
Amber:)


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=502471

Hide/Unhide Rows
 

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.

"Al" wrote:

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

Hide/Unhide Rows
 

So how would I fix this?
Thanks!

"Toppers" wrote:

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.

"Al" wrote:

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