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

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

"Harold Good" wrote:

> 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
>
>
>

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

"Harold Good" wrote:

> 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
>
>
>

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

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


"Matthew Herbert" <(E-Mail Removed)> wrote in
message news:A2F52998-0B6B-40B6-A562-(E-Mail Removed)...
> 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
>
> "Harold Good" wrote:
>
>> 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
>>
>>
>>

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

Just look in the vba help index for SPECIALCELLS

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

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

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
newsE860EEF-14D0-4E8B-9580-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "Harold Good" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> > news:u$(E-Mail Removed)...
>> >> How about a nice one liner
>> >> Sub deleteblankrows()
>> >> Range("a1:a6").SpecialCells(xlCellTypeBlanks).EntireRow.hidden=true
>> >> End Sub
>> >> --
>> >> Don Guillett
>> >> Microsoft MVP Excel
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "Harold Good" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >>> 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
>> >>>
>> >>
>> >
>> >
>>
>>

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.