Macro to Hide Rows

[billythedj66]

Hello,

I have a worksheet named "IGE". This worksheet is used as a printout sheet that gets it's data from various other sheets within the workbook. The range of cells are A1:H343. What I am trying to do is have a macro that if the cell in column "A" of a particular row is blank, then the whole row is hidden, if not then the row is visible and has autofit applied to it. I have the following macro, but it takes about two minutes to complete:

Sub IGE()

' IGE Macro
' Edited 1/22/2003 by Bill

Sheets("IGE").Visible = True
Sheets("IGE").Select
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("A1:A" & Range("A344").End(xlUp).Row)
If c.Value = "" Then
c.EntireRow.Hidden = True
Else: c.EntireRow.AutoFit
End If
Next c
Sheets("IGE").PrintOut
ActiveSheet.UsedRange.EntireRow.Hidden = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Sheets("IGE").Visible = False
Sheets("Opening").Select
Range("B17").Select


End Sub


Any ideas on why it is so slow, or is there a better way to do the same function?

Thanks in Advance!!

 

[Von Pookie]

Untested, but does this work any better for you?

Sub IGE()
Dim i As Range, lRow As Long

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

With Sheets("IGE")
    .Visible = True
    lRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lRow
        If .Cells(i, 1) = "" Then
            .Rows(i).Hidden = True
        Else:
            .Rows(i).AutoFit
        End If
    Next i
    .PrintOut
    .UsedRange.EntireRow.Hidden = False
    .Visible = False
End With

With Application
    .Goto Sheets("Opening").Range("B17")
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

 

[billythedj66]

I tried that macro, but when I ran it I received the following error:

Compile Error:

Type mismatch

Then the debugger highlights the lower case "i" in the following:

For i = 1 To lRow

Bill

 

[Von Pookie]

Oops! Sorry about that. Change:

Dim i As Range

to

Dim i As Long.