Excel VBA - Hidding a range

[WhiteD3]

Hiya,

I want to hide a range of rows on a spreadsheet. The user can add and delete rows so the range changes.

The original code (written by someone else) worked but hid a row at a time, which takes forever to execute when you've got a 1000+ rows.

However, I can't work out how to hide a range.

Would appreciate any help.

Cheers.

Sub ShowHideAll()
ActiveSheet.Unprotect
Firstrow = 3
lastrow = Range("EndRow").Row - 1

If ActiveSheet.btnAll.Caption = "Hide All" Then 'Checks the caption on the show/hide button.
ActiveSheet.Range(Cells(Firstrow, 1), Cells(lastrow, 12)).Hidden = True
ElseIf ActiveSheet.btnAll.Caption = "Show All" Then
ActiveSheet.Range(Cells(Firstrow, 1), Cells(lastrow, 12)).Hidden = False
End If
Cells(2, 1).Activate
ActiveSheet.Protect
End Sub

[WhiteD3]

Figured it out.....

Sub ShowHideAll()
Dim RangeToHide As Range
ActiveSheet.Unprotect
Firstrow = 3
lastrow = Range("EndRow").Row - 1
Set RangeToHide = ActiveSheet.Range(Cells(Firstrow, 1), Cells(lastrow, 12))
If ActiveSheet.btnAll.Caption = "Hide All" Then 'Checks the caption on the show/hide button.
RangeToHide.EntireRow.Hidden = True
ActiveSheet.btnAll.Caption = "Show All"
ActiveSheet.btnPending.Caption = "Show Pending"
ActiveSheet.btnProgress.Caption = "Show In Progress"
ActiveSheet.btnDLP.Caption = "Show DLP"
ActiveSheet.btnComplete.Caption = "Show Complete"
ActiveSheet.btnLost.Caption = "Show Lost"
ElseIf ActiveSheet.btnAll.Caption = "Show All" Then
RangeToHide.EntireRow.Hidden = False
ActiveSheet.btnAll.Caption = "Hide All"
ActiveSheet.btnPending.Caption = "Hide Pending"
ActiveSheet.btnProgress.Caption = "Hide In Progress"
ActiveSheet.btnDLP.Caption = "Hide DLP"
ActiveSheet.btnComplete.Caption = "Hide Complete"
ActiveSheet.btnLost.Caption = "Hide Lost"
End If
Cells(2, 1).Activate
ActiveSheet.Protect
End Sub

[Rosco]

:eek2::blink: