View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
K_Macd K_Macd is offline
external usenet poster
 
Posts: 54
Default code to hide rows based on criteria - but without looping

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.

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"Don Guillett" wrote:

Just look in the vba help index for SPECIALCELLS

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harold Good" wrote in message
...
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" wrote in message
...
How about a nice one liner
Sub deleteblankrows()
Range("a1:a6").SpecialCells(xlCellTypeBlanks).Enti reRow.hidden=true
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harold Good" wrote in message
...
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