code to hide rows based on criteria - but without looping

[Harold Good]

Hi,

I'd like to hide unused rows in a budget form based on a formula in Col F of
any row in the range below that is equal to "". I know little about VBA,
but the code below works, thhough it is too slow to do everytime it
recalculates. Because Column F values are entered from a separate worksheet,
I cannot use the Worksheet_Change Event.

Private Sub Worksheet_Calculate()
Dim cell As Range
Application.EnableEvents = False
For Each cell In Range("F9:F98")
If cell.Value = "" Then
cell.EntireRow.Hidden = True
End If
Next cell
Application.EnableEvents = True
End Sub

What I've read elsewhere is to avoid Loops whenever possible. Since all the
rows that do not equal "" are at the top, and all those that equal "" are at
the bottom, is there a better way to do this using CountA, or SpecialCells?

Thanks for any help you can offer,
Harold

 

[Matthew Herbert]

Harold,

I realized I wrote the code to delete rows, not to hide rows. I apologize
for not reading your post carefully enough. Simply exchange the
..EntrieRow.Delete with .EntrieRow.Hidden = True.

Best,

Matt

 

[Matthew Herbert]

Harold,

I've included the two separate ways below that you mention. In general,
performing methods on an aggregate basis rather than a one-by-one basis is
fastest, thus, you'll see the Union function below. In your situation, when
you delete one-by-one, Excel may be recalculating after every deletion, so
you may have up to 90 different recalculations on a one-by-one basis rather
than 1 recalculation on the aggregate. You can loop one-by-one if you so
desire because a loop of 90 items is practically negligible. (Also, I would
comment the .EntireRow.Delete syntax below and uncomment the Debug.Print
lines. Debug.Print will print to the Immediate Window (View | Immediate
Window) and will allow you to see how the program is behaving prior to
executing a deletion).

Best,

Matthew Herbert

Sub TestDeleteBlanks()
Dim rngEval As Range
Dim rngDelete As Range
Dim rngCell As Range

Set rngEval = ActiveSheet.Range("F9:F98")
Set rngDelete = rngEval.SpecialCells(xlCellTypeBlanks)
'Debug.Print rngDelete.Address
rngDelete.EntireRow.Delete

Set rngDelete = Nothing

'-OR-

For Each rngCell In rngEval.Cells
If rngCell.Value = "" Then
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else
Set rngDelete = Union(rngDelete, rngCell)
End If
End If
Next rngCell

'Debug.Print rngDelete.Address
rngDelete.EntireRow.Delete

End Sub

 

[Don Guillett]

How about a nice one liner
Sub deleteblankrows()
Range("a1:a6").SpecialCells(xlCellTypeBlanks).EntireRow.hidden=true
End Sub

 

[Harold Good]

Thanks very much Matthew, I shall give this a try tomorrow!
Harold

 

[Harold Good]

Thank you Don, I will try this out. So simple I need to study it to see what
all it's doing!

Harold

 

[Don Guillett]

Just look in the vba help index for SPECIALCELLS

 

[K_Macd]

Much better to have collective operations rather than loops.

Also when running any macro working with a large data set or complex
calculations consider setting the following off then resetting on after
completion.

Application.ScreenUpdating = False/True
Application.Calculation = xlCalculationManual/xlCalculationAutomatic
Application.EnableEvents = False/True

The most significant of these is the first with only incremental gains from
the others.

 

[Harold Good]

Thanks Ken for this great reminder. I knew about the screen updating but
thought it was only to make it look nicer during the process. I had no idea
it'd impact speed. I did a rough timing of it to go thru the 90 cells, it
took about 4 times longer with updating = true.

Great suggestion.
Harold


"K_Macd" <kmacdonald "A_T" activ8 ''''''''''''''''''''''''''''''''D O
T'''''''''''''''''''''''''''''''' net [S> wrote in message

 

[Phil Hibbs]

This will select from F9 downwards to the end of the contiguous set of
non-blank cells:

Private Sub Worksheet_Calculate()
Dim cells As Range
Dim cell As Range
Application.EnableEvents = False
Range("F9").Select
Selection.End(xlDown).Select
For Each cell In Range("F9:F" & Trim(Selection.Row))
If cell.Value = "" Then
cell.EntireRow.Hidden = True
End If
Next cell
Application.EnableEvents = True
End Sub

I think the solutions posted by others are superior, but I thought
this was an interesting way of avoiding processing blank rows if you
do need to do something more complex that needs a loop.

Phil Hibbs.

 

[Harold Good]

Hi, I have tried this and the other suggestion offered by Matthew Herbert.
Neither seem to work and I think the problem might be that all cells in this
F range have formulas in them. In the ones I want to hide, the formulas
solve to "" so they appear blank to the user.

But it appears that SpecialCells(xlCellTypeBlanks) does not count these as
blank cells. If I delete the formula then the code below works.

Is there a way to make this work so that if any cells in this range = "",
they will be hidden?

Thanks again for your help,
Harold

 

[Harold Good]

Thanks for this Phil, I see this will avoid unnecessary looping which would
be helpful. So many different ways to do these things!

I'll wait to see if anyone can help with the SpecialCells track I was
pursuing.

Harold

 

[Matthew Herbert]

Harold,

I created dummy values in column E and input an IF function in column F
(e.g. =IF(E9="","",E9)). When I used the For Each loop (listed below), I
didn't have any trouble hiding the rows. Test this code and see if you get
the same results; otherwise, you might need to provide more details regarding
your function, whether rows are already hidden, etc.

Best,

Matt

Sub TestHideRows()
Dim rngEval As Range
Dim rngHide As Range
Dim rngCell As Range

Set rngEval = ActiveSheet.Range("F9:F98")

For Each rngCell In rngEval.Cells
If rngCell.Value = "" Then
If rngHide Is Nothing Then
Set rngHide = rngCell
Else
Set rngHide = Union(rngHide, rngCell)
End If
End If
Next rngCell

Debug.Print rngHide.Address
rngHide.EntireRow.Hidden = True

End Sub

 

[Harold Good]

Thanks Matt, I had only tried your first one, not your second one. This
second one with the Union works great and is very fast.

Thank you for introducing me to Union, and for your kind help as well. Now
I'm off and running.

Have a great day!
Harold