Button to hide/unhide all empty rows? (Excel 97)

[Shadowmis]

Hello, I am very new to macros so please bear with me. I am trying to make a button to hide all the empty rows and another to unhide them for a report in Excel 97. I have tried using two command buttons but toggle would work too. In any case I am not sure how to proceed. I have code I have tried to adapt from another macro I found on this site that hides all empty rows upon activating the sheet. However I get a runtime error '1004' application-defined or object defined error near the bottom (red text). However I thought it was defined for hiddenrow? As it isn't working I must be wrong however I am not sure how to fix it.
Any ideas would be greatly appreciated.

Private Sub CommandButton2_Click()
If CommandButton2.Value = True Then
Dim HiddenRow&, RowRange As Range, RowRangeValue&
Const FirstRow As Long = 4
Const LastRow As Long = 180
Const FirstCol As String = "A"
Const LastCol As String = "O"
ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
Rows(HiddenRow).EntireRow.Hidden = False
Else
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Else
Rows(HiddenRow).EntireRow.Hidden = False
End If
ActiveWindow.DisplayZeros = True
Application.ScreenUpdating = True
End Sub

Thanks

[mdmackillop]

Hi ShadowMis
Welcome to VBAX.
If you select your code and click on the VBA button, it formats the code as shown.

Try the following revision to your code. If you assign this to a Forms Toolbar button, this will act to toggle between hidden and visible rows.

Sub HideRows()
Dim HiddenRow&, RowRange As Range, RowRangeValue&, i As Long
Const FirstRow As Long = 4
    Const LastRow As Long = 180
    Const FirstCol As String = "A"
    Const LastCol As String = "F"
'Check for hidden rows
    For i = FirstRow To LastRow
        If Rows(i).Hidden = True Then
            Rows(FirstRow & ":" & LastRow).Hidden = False
            Exit Sub
        End If
    Next
'Hide blank rows if none are hidden
    ActiveWindow.DisplayZeros = False
    Application.ScreenUpdating = False
    For HiddenRow = FirstRow To LastRow
        Set RowRange = Range(FirstCol & HiddenRow & _
        ":" & LastCol & HiddenRow)
        RowRangeValue = Application.WorksheetFunction.CountBlank(RowRange)
        If RowRangeValue <> Cells(1, LastCol).Column - Cells(1, FirstCol).Column + 1 Then
            Rows(HiddenRow).EntireRow.Hidden = False
        Else
            Rows(HiddenRow).EntireRow.Hidden = True
        End If
    Next HiddenRow
ActiveWindow.DisplayZeros = True
    Application.ScreenUpdating = True
End Sub

[Shadowmis]

Thanks, I will get back to you to let you know how it works out.

[Shadowmis]

Humm.. It says I am Unable to set the hidden property for the range class (Runtime error 1004) for "Rows(FirstRow & ":" & LastRow).Hidden = False"

[mdmackillop]

Try changing to the following
Rows(FirstRow & ":" & LastRow).EntireRow.Hidden = False

[Shadowmis]

Shakes head, no that doesn't seem to work either...
I think I am going to take a break on this macro and try some others that I want to do. Then maybe I will get some more experience and new insight that will help me with this one. I will let you know how it works out.
Thanks for your help

[mdmackillop]

It's probably an incompatability with Excel 97.

[Shadowmis]

Anyone have some ideas about what might work on a dinosuar?
Thanks

[MOS MASTER]

Hi,

I ran that code on Excel 97 and it run's like a knife through butter.

So something else must be the matter here...

Could you post a test document that gives you the error?

[Steiner]

Hi,
my Excel97 brings such an error only when the sheet is locked, maybe that's the problem?

Daniel

[Ken Puls]

Hi guys,

Since this looks like it is a continuing discussion, I took the liberty of merging the threads and retitling it to reference the Excel 97 portion.

Hope no one minds, but I also cut two comments to make the thread flow again.

Cheers,

[Shadowmis]

Thank Kpuls
Alright here is the report with the code, first run, as is, I get an error about not displaying zeros, so I removed it (which I don't want to do but for the sake of testing) then running it again, I get the unable to set hidden property error. Also, my report isn't locked. (At least I don't think it is locked, it isn't protected, or even shared)

[Insomniac]

This is a bug in xl97.
If you were using a CommandButton you could set the TakeFocusOnClick Property to False.
Since the ToggleButton does not have this property you will need to use the less preffered workaround of

Activecell.Activate

as the 1st line in the code.

see:http://support.microsoft.com/default...&Product=xlw97

(If you run the ToggleButton1_Click() sub directly from the VBE as it stands it will run fine, the problem is when run from the sheet & the button has the focus.)

[Shadowmis]

Wow. Thanks Insomniac, and Everyone! It is now working great and I think I understand it enough to do similar buttons on my other reports.

[Ken Puls]

Hey Insomniac!

Very nice catch!

[MOS MASTER]

Wow. Thanks Insomniac, and Everyone! It is now working great and I think I understand it enough to do similar buttons on my other reports.

Glad to see you have it working now!