PDA

View Full Version : [SOLVED] Button to hide/unhide all empty rows? (Excel 97)



Shadowmis
08-11-2005, 02:13 PM
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
08-11-2005, 04:00 PM
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
08-12-2005, 08:29 AM
Thanks, I will get back to you to let you know how it works out.

Shadowmis
08-12-2005, 12:03 PM
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
08-12-2005, 12:08 PM
Try changing to the following
Rows(FirstRow & ":" & LastRow).EntireRow.Hidden = False

Shadowmis
08-15-2005, 09:17 AM
Shakes head, no that doesn't seem to work either... :banghead:
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
08-15-2005, 10:23 AM
It's probably an incompatability with Excel 97.

Shadowmis
08-15-2005, 11:23 AM
Anyone have some ideas about what might work on a dinosuar?
Thanks

MOS MASTER
08-15-2005, 12:54 PM
Hi, :yes

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? :whistle:

Steiner
08-15-2005, 11:06 PM
Hi,
my Excel97 brings such an error only when the sheet is locked, maybe that's the problem?

Daniel

Ken Puls
08-15-2005, 11:14 PM
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
08-16-2005, 08:41 AM
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
08-16-2005, 09:13 AM
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.aspx?scid=kb;en-us;177527&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
08-16-2005, 09:33 AM
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.:clap:

Ken Puls
08-16-2005, 09:35 AM
Hey Insomniac!

Very nice catch! :thumb

MOS MASTER
08-16-2005, 10:43 AM
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.:clap:

Glad to see you have it working now! :yes