Hide rows issue

[Cerberus]

I have a code I wrote to hide rows in a 2066 row spreadsheet but every time I
run the code, it takes about ten minutes and it hides rows all the way up to
row 7760. I have deleted everything in the spreadsheet beyond row 2066 and
cleared and possible fromating but it still hides everything to row 7760. Is
there a way to have it stop running after the last row that has a value?

Here is the code:
Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range

'< Set the column that contains data >
Const DataCol As String = "A"

'****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = 1 To ActiveSheet.UsedRange.Rows.Count

'Take the Value of the cells in column A of each row
If Val(Range(DataCol & HiddenRow).Value) <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
'Take the Length of the value in column A of each row
ElseIf Len(Range(DataCol & HiddenRow).Value) = 14 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub

 

[Bob Umlas]

You can do this almost instantly with the help of a helper column used
temporarily. If all you want to do is hide rows in which column A has
nothing in it, and assuming column H (arbitrary) is available:
Right-click the sheet tab, select View Code, enter this:


Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Rows.Hidden = False
With Range("H1:H2066")
.FormulaR1C1 = "=if(len(rc1)=0,1,na())"
Calculate
.SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Hidden = True
.ClearContents
End With
Application.ScreenUpdating = True
End Sub


HTH
Bob Umlas
Excel MVP

 

[Cerberus]

Thank you Bob for looking into this for me. I pluged in your code came up
with an error Run-time error '1004' No cells were found. When I debug it
highlights: .SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Hidden =
True

Is the .SpecialCells where I reference column A?

Sorry to possibly ask such a silly question.

Thanks again.

 

[Gord Dibben]

After deleting entire unused rows and columns did you save the workbook to
reset the usedrange?


Gord Dibben MS Excel MVP