Excel 2000 Macro conversion to Excel 97 1

[LBryant777]

I created two macros in Excel 2000 that hides and unhides rows that do not have shading in them and they are assigned to a toggle button. However, when the same macro is executed in Excel 97, users get a Runtime 1004 error that says "Unable to set the Hidden property of the Range class." Here is the code:

Sub HideRows()

Application.ScreenUpdating = False
    
Dim rge As Range
Set rge = ActiveSheet.UsedRange

For r = 1 To rge.Rows.Count
    For c = 1 To rge.Columns.Count
        If rge(r, c).Interior.ColorIndex = xlNone Then
            rge.Rows(r).Hidden = True
            Exit For
        End If
    Next c
Next r
Set rge = Nothing
End Sub

Sub UnhideRows()
Dim rge As Range
Application.ScreenUpdating = False
Set rge = ActiveSheet.UsedRange
rge.Rows.Hidden = False
Set rge = Nothing
End Sub

Is there something that is not compatible in my 2000 VBA that would cause this not to execute in 97? Any help would be appreciated - I've got about 450 users that are waiting for this to be resolved! Thanks!

 

[SkipVought]

Hi,

Just select any cell (deselect the button)

Sub HideRows()

Application.ScreenUpdating = False
    
Dim rge As Range
Set rge = ActiveSheet.UsedRange
[a1].Select
For r = 1 To rge.Rows.Count
    For c = 1 To rge.Columns.Count
        If rge(r, c).Interior.ColorIndex = xlNone Then
            rge.Rows(r).Hidden = True
            Exit For
        End If
    Next c
Next r
Set rge = Nothing
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884