Hiding Empty Rows

  • Thread starter Thread starter AJ Master
  • Start date Start date
A

AJ Master

I have been using the code below to hide empty rows in a worksheet.
Originally I was only evaluating 12 columns (one for each month of
the year) in each of about 100 rows to determine whether to hide the
entire row. The number of rows has grown to about 350 and now the
code runs rather slow. About 7-10secs versus the 2 secs it used to
take. Anyone with any ideas on how to improved the speed of this
code??

Dim bytCol As Byte
Dim lngRow As Integer
Application.ScreenUpdating = False

For lngRow = 8 To 359 'All Rows
For bytCol = 4 To 15 'Columns D-O
If ActiveSheet.Cells(lngRow, bytCol) <> 0 Then Exit For
If bytCol = 15 Then ActiveSheet.Rows(lngRow).Hidden = True
Next bytCol
Next lngRow

Application.ScreenUpdating = True

Thanks....AJ
 
Try this. It ran in less than a second for me. Let me know how it goes.

Sub Test_Hide_Rows()

Dim lngRow As Integer
Application.ScreenUpdating = False
With ActiveSheet
For lngRow = 8 To 359 'All Rows
If .Cells(lngRow, 4) = 0 Then
If .Cells(lngRow, 4).End(xlToRight).Column > 15 Then
.Cells(lngRow, 4).EntireRow.Hidden = True
End If
End If
Next lngRow
End With
Application.ScreenUpdating = True

End Sub

Regards,

OssieMac
 
Try this. It ran in less than a second for me. Let me know how it goes.

Sub Test_Hide_Rows()

Dim lngRow As Integer
Application.ScreenUpdating = False
With ActiveSheet
For lngRow = 8 To 359 'AllRows
If .Cells(lngRow, 4) = 0 Then
If .Cells(lngRow, 4).End(xlToRight).Column > 15 Then
.Cells(lngRow, 4).EntireRow.Hidden = True
End If
End If
Next lngRow
End With
Application.ScreenUpdating = True

End Sub

Regards,

OssieMac
Click to expand...

OssieMac,

Thanks for your quick reply. I tried your code, but its hiding rows
that it shouldn't. I have to check every column D thru O (ie. 4 to
15) to see if the value is something other than zero. If the value in
each column (D thru O) has a zero value then I can hide the row. Any
tweak that might fix your code?
 
How about:

Option Explicit
Sub Test_Hide_Rows2()

Dim iRow As Long
Dim myRngToCheck As Range
Dim HowManyCols As Long

HowManyCols = 12 'D to O is 12 columns
With ActiveSheet
For iRow = 8 To 359 'All Rows
Set myRngToCheck = .Cells(iRow, 4).Resize(1, HowManyCols)
If Application.CountIf(myRngToCheck, 0) = HowManyCols Then
'all 0!
.Rows(iRow).Hidden = True
End If
Next iRow
End With

End Sub
 
How about:

Option Explicit
Sub Test_Hide_Rows2()

Dim iRow As Long
Dim myRngToCheck As Range
Dim HowManyCols As Long

HowManyCols = 12 'D to O is 12 columns
With ActiveSheet
For iRow = 8 To 359 'All Rows
Set myRngToCheck = .Cells(iRow, 4).Resize(1, HowManyCols)
If Application.CountIf(myRngToCheck, 0) = HowManyCols Then
'all 0!
.Rows(iRow).Hidden = True
End If
Next iRow
End With

End Sub
Click to expand...

Dave,

Thanks very much! The new code works great and I'm back to the 2-3
sec execution range.

Regards...AJ
 
Hello

i have a similar situation. i am using excel to import a crosstab query from
access. i have a autofilter on the results in the spreadsheet. i want to be
able to hide columns based on whether or not there is data below the title of
the column, so if column e is empty from rows 2 to 65536 i want it to hide
that column. i would like it to happen automattically after a filter from the
autofilter is applied, but a button would do.

if i filter a column using the auto filter some columns become "empty". yes
there is still stuff there in the empty columns, but they are being filtered
leaving gaps in the columns. is there a way to use this code to hide those
columns that dont show anything?

i tried just "reversing" the code to work on columns, but those columns
arent really empty, so i the code doesnt work.

here was my attempt:

Sub testcolumnhide()
Dim iColumn As Long
Dim myRngtoCheck As Range
Dim HowManyRows As Long

HowManyRows = 65533
With ActiveSheet
For iColumn = 5 To 250
Set myRngtoCheck = .Cells(4, iColumn).Resize(HowManyRows, 1)
If Application.CountIf(myRngtoCheck, 0) = HowManyRows Then
.Columns(iColumn).Hidden = True
End If
Next iColumn
End With

End Sub


thanks for any and all help!
 
I think you're doing...

Filter the range
look at each column in the filtered range
if there are any non-empty cells in the visible cells in that column
(discounting the header), then keep that column visible.

If there are only empty cells in the visible cells in that column, then hide
that column.

If that's correct:

Option Explicit
Sub testcolumnhide()
Dim myCol As Range
Dim wks As Worksheet
Dim myRng As Range

Set wks = ActiveSheet

With wks.AutoFilter.Range
'avoid the headers by coming down one
Set myRng = .Resize(.Rows.Count - 1).Offset(1, 0)
End With

For Each myCol In myRng.Columns
myCol.EntireColumn.Hidden = CBool(Application.Subtotal(3, myCol) = 0)
Next myCol

End Sub

=====
I think you'd have to do something in your code to make sure you were looking at
just the visible cells. You could use .cells.specialcells(xlcelltypevisible),
but =subtotal(3,...) ignores rows hidden by data|filter. So I used that.
 
Hello

thanks. you are the man! :)

Dave Peterson said:
I think you're doing...

Filter the range
look at each column in the filtered range
if there are any non-empty cells in the visible cells in that column
(discounting the header), then keep that column visible.

If there are only empty cells in the visible cells in that column, then hide
that column.

If that's correct:

Option Explicit
Sub testcolumnhide()
Dim myCol As Range
Dim wks As Worksheet
Dim myRng As Range

Set wks = ActiveSheet

With wks.AutoFilter.Range
'avoid the headers by coming down one
Set myRng = .Resize(.Rows.Count - 1).Offset(1, 0)
End With

For Each myCol In myRng.Columns
myCol.EntireColumn.Hidden = CBool(Application.Subtotal(3, myCol) = 0)
Next myCol

End Sub

=====
I think you'd have to do something in your code to make sure you were looking at
just the visible cells. You could use .cells.specialcells(xlcelltypevisible),
but =subtotal(3,...) ignores rows hidden by data|filter. So I used that.
Click to expand...
 
Back
Top