Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hiding row when cell is blank

Status
Not open for further replies.

Asspin

Technical User
Jan 17, 2005
155
US
I am trying to hide a complete row if the cell in column A is blank for a given range. Reports is the name of the sheet I am working with. This code seems to run very slow, any idea how to speed it up?

This is what I am working with:

Code:
    Dim r As Integer
        For r = 6 To 36
            If Reports.Cells(r, 1) = "" Then Rows(r).EntireRow.Hidden = True
        Next

Dan
 
What about this ?
If Reports.Cells(r, 1) = "" Then [!]Reports.[/!]Rows(r).EntireRow.Hidden = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Rather than looping, I'd suggest that you just hide all rows where the target column is empty.

Like this:
Code:
Sub HideWhereColAIsEmpty
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
end sub

Less code, and it will run faster than looping too!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
PHV, that works at the same speed.
anotherhiggins, your solution seems about the same speed but hides rows that I need. :)

Dan
 
Well, for 30 rows you aren't going to notice a difference. But try it on 3,000.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
Guess I am just stuck for the speed, not sure why it takes so long though. Annoying.

Dan
 
And what about this ?
Application.ScreenUpdating = False
For r = 6 To 36
If Reports.Cells(r, 1) = "" Then Reports.Rows(r).EntireRow.Hidden = True
Next
Application.ScreenUpdating = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
ScreenUpdating is already disabled, this bit of code is being called from a very large module. I just don't understand why it takes so long to run this on around 30 rows. This bit of code is around 10 seconds to run.

Dan
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top