PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Automatically hide rows with 0 value....faster?

Reply
Thread Tools Rate Thread

Automatically hide rows with 0 value....faster?

 
 
Dan Brimley
Guest
Posts: n/a
 
      4th Aug 2008
Hi,

I used this code in VB to automatically hide rows that have formulas
resulting in a value of zero:

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Or cell.Value = 0 Then _
cell.EntireRow.Hidden = True
Next cell
End With
stoppit:
Application.EnableEvents = True
End Sub

Then I decided that I would rather have it so I can hide or unhide manually,
so I created macros with hide and show buttons with this code:
Sub Hide()
On Error GoTo stoppit
Application.EnableEvents = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Or cell.Value = 0 Then _
cell.EntireRow.Hidden = True
Next cell
End With
stoppit:
Application.EnableEvents = True
End Sub
------------------------------
Sub Show()
On Error GoTo stoppit
Application.EnableEvents = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Or cell.Value = 0 Then _
cell.EntireRow.Hidden = False
Next cell
End With
stoppit:
Application.EnableEvents = True
End Sub

With the macros, this runs slower than desired. Is there a way to keep it
in VB and still allow a way to use the hide / show buttons, so it's not
automatic? It runs much faster thru VB.

Thanks,
Dan
 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      4th Aug 2008
Filtering should be faster.

Sub hidezero()
Lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:a" & Lr).AutoFilter Field:=1, Criteria1:="<>0"
'Range("A1:a" & Lr).AutoFilter' to unfilter
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Dan Brimley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I used this code in VB to automatically hide rows that have formulas
> resulting in a value of zero:
>
> Private Sub Worksheet_Calculate()
> On Error GoTo stoppit
> Application.EnableEvents = False
> With ActiveSheet.UsedRange
> .Rows.Hidden = False
> For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
> If cell.Text = "" Or cell.Value = 0 Then _
> cell.EntireRow.Hidden = True
> Next cell
> End With
> stoppit:
> Application.EnableEvents = True
> End Sub
>
> Then I decided that I would rather have it so I can hide or unhide
> manually, so I created macros with hide and show buttons with this code:
> Sub Hide()
> On Error GoTo stoppit
> Application.EnableEvents = False
> With ActiveSheet.UsedRange
> .Rows.Hidden = False
> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
> If cell.Text = "" Or cell.Value = 0 Then _
> cell.EntireRow.Hidden = True
> Next cell
> End With
> stoppit:
> Application.EnableEvents = True
> End Sub
> ------------------------------
> Sub Show()
> On Error GoTo stoppit
> Application.EnableEvents = False
> With ActiveSheet.UsedRange
> .Rows.Hidden = False
> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
> If cell.Text = "" Or cell.Value = 0 Then _
> cell.EntireRow.Hidden = False
> Next cell
> End With
> stoppit:
> Application.EnableEvents = True
> End Sub
>
> With the macros, this runs slower than desired. Is there a way to keep it
> in VB and still allow a way to use the hide / show buttons, so it's not
> automatic? It runs much faster thru VB.
>
> Thanks,
> Dan
>
>
 
Reply With Quote
 
Dan Brimley
Guest
Posts: n/a
 
      5th Aug 2008
Thanks. Does this give me the ability to manually filter or unfilter the
zeros? How would I do that? Also, if a value is less than zero, I don't
want to filter it. I forgot to mention that in my original post.

Thanks



"Don Guillett" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Filtering should be faster.
>
> Sub hidezero()
> Lr = Cells(Rows.Count, "a").End(xlUp).Row
> Range("A1:a" & Lr).AutoFilter Field:=1, Criteria1:="<>0"
> 'Range("A1:a" & Lr).AutoFilter' to unfilter
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Dan Brimley" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi,
>>
>> I used this code in VB to automatically hide rows that have formulas
>> resulting in a value of zero:
>>
>> Private Sub Worksheet_Calculate()
>> On Error GoTo stoppit
>> Application.EnableEvents = False
>> With ActiveSheet.UsedRange
>> .Rows.Hidden = False
>> For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
>> If cell.Text = "" Or cell.Value = 0 Then _
>> cell.EntireRow.Hidden = True
>> Next cell
>> End With
>> stoppit:
>> Application.EnableEvents = True
>> End Sub
>>
>> Then I decided that I would rather have it so I can hide or unhide
>> manually, so I created macros with hide and show buttons with this code:
>> Sub Hide()
>> On Error GoTo stoppit
>> Application.EnableEvents = False
>> With ActiveSheet.UsedRange
>> .Rows.Hidden = False
>> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
>> If cell.Text = "" Or cell.Value = 0 Then _
>> cell.EntireRow.Hidden = True
>> Next cell
>> End With
>> stoppit:
>> Application.EnableEvents = True
>> End Sub
>> ------------------------------
>> Sub Show()
>> On Error GoTo stoppit
>> Application.EnableEvents = False
>> With ActiveSheet.UsedRange
>> .Rows.Hidden = False
>> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
>> If cell.Text = "" Or cell.Value = 0 Then _
>> cell.EntireRow.Hidden = False
>> Next cell
>> End With
>> stoppit:
>> Application.EnableEvents = True
>> End Sub
>>
>> With the macros, this runs slower than desired. Is there a way to keep
>> it in VB and still allow a way to use the hide / show buttons, so it's
>> not automatic? It runs much faster thru VB.
>>
>> Thanks,
>> Dan
>>
>>
>
 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      5th Aug 2008
If you want to filter out <0 and 0 just change
:="<>0"
to
:=">0"

Of course you can still manually use the filter. To manually unfilter
data>filter>autofilter
or put the last line in a separate macro

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Dan Brimley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks. Does this give me the ability to manually filter or unfilter the
> zeros? How would I do that? Also, if a value is less than zero, I don't
> want to filter it. I forgot to mention that in my original post.
>
> Thanks
>
>
>
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Filtering should be faster.
>>
>> Sub hidezero()
>> Lr = Cells(Rows.Count, "a").End(xlUp).Row
>> Range("A1:a" & Lr).AutoFilter Field:=1, Criteria1:="<>0"
>> 'Range("A1:a" & Lr).AutoFilter' to unfilter
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Dan Brimley" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Hi,
>>>
>>> I used this code in VB to automatically hide rows that have formulas
>>> resulting in a value of zero:
>>>
>>> Private Sub Worksheet_Calculate()
>>> On Error GoTo stoppit
>>> Application.EnableEvents = False
>>> With ActiveSheet.UsedRange
>>> .Rows.Hidden = False
>>> For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
>>> If cell.Text = "" Or cell.Value = 0 Then _
>>> cell.EntireRow.Hidden = True
>>> Next cell
>>> End With
>>> stoppit:
>>> Application.EnableEvents = True
>>> End Sub
>>>
>>> Then I decided that I would rather have it so I can hide or unhide
>>> manually, so I created macros with hide and show buttons with this code:
>>> Sub Hide()
>>> On Error GoTo stoppit
>>> Application.EnableEvents = False
>>> With ActiveSheet.UsedRange
>>> .Rows.Hidden = False
>>> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
>>> If cell.Text = "" Or cell.Value = 0 Then _
>>> cell.EntireRow.Hidden = True
>>> Next cell
>>> End With
>>> stoppit:
>>> Application.EnableEvents = True
>>> End Sub
>>> ------------------------------
>>> Sub Show()
>>> On Error GoTo stoppit
>>> Application.EnableEvents = False
>>> With ActiveSheet.UsedRange
>>> .Rows.Hidden = False
>>> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
>>> If cell.Text = "" Or cell.Value = 0 Then _
>>> cell.EntireRow.Hidden = False
>>> Next cell
>>> End With
>>> stoppit:
>>> Application.EnableEvents = True
>>> End Sub
>>>
>>> With the macros, this runs slower than desired. Is there a way to keep
>>> it in VB and still allow a way to use the hide / show buttons, so it's
>>> not automatic? It runs much faster thru VB.
>>>
>>> Thanks,
>>> Dan
>>>
>>>
>>
>
>
 
Reply With Quote
 
Dan Brimley
Guest
Posts: n/a
 
      6th Aug 2008
Great, that will work. Thanks for your help!


"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If you want to filter out <0 and 0 just change
> :="<>0"
> to
> :=">0"
>
> Of course you can still manually use the filter. To manually unfilter
> data>filter>autofilter
> or put the last line in a separate macro
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Dan Brimley" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thanks. Does this give me the ability to manually filter or unfilter the
>> zeros? How would I do that? Also, if a value is less than zero, I don't
>> want to filter it. I forgot to mention that in my original post.
>>
>> Thanks
>>
>>
>>
>> "Don Guillett" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> Filtering should be faster.
>>>
>>> Sub hidezero()
>>> Lr = Cells(Rows.Count, "a").End(xlUp).Row
>>> Range("A1:a" & Lr).AutoFilter Field:=1, Criteria1:="<>0"
>>> 'Range("A1:a" & Lr).AutoFilter' to unfilter
>>> End Sub
>>>
>>> --
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> (E-Mail Removed)
>>> "Dan Brimley" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Hi,
>>>>
>>>> I used this code in VB to automatically hide rows that have formulas
>>>> resulting in a value of zero:
>>>>
>>>> Private Sub Worksheet_Calculate()
>>>> On Error GoTo stoppit
>>>> Application.EnableEvents = False
>>>> With ActiveSheet.UsedRange
>>>> .Rows.Hidden = False
>>>> For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
>>>> If cell.Text = "" Or cell.Value = 0 Then _
>>>> cell.EntireRow.Hidden = True
>>>> Next cell
>>>> End With
>>>> stoppit:
>>>> Application.EnableEvents = True
>>>> End Sub
>>>>
>>>> Then I decided that I would rather have it so I can hide or unhide
>>>> manually, so I created macros with hide and show buttons with this
>>>> code:
>>>> Sub Hide()
>>>> On Error GoTo stoppit
>>>> Application.EnableEvents = False
>>>> With ActiveSheet.UsedRange
>>>> .Rows.Hidden = False
>>>> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
>>>> If cell.Text = "" Or cell.Value = 0 Then _
>>>> cell.EntireRow.Hidden = True
>>>> Next cell
>>>> End With
>>>> stoppit:
>>>> Application.EnableEvents = True
>>>> End Sub
>>>> ------------------------------
>>>> Sub Show()
>>>> On Error GoTo stoppit
>>>> Application.EnableEvents = False
>>>> With ActiveSheet.UsedRange
>>>> .Rows.Hidden = False
>>>> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
>>>> If cell.Text = "" Or cell.Value = 0 Then _
>>>> cell.EntireRow.Hidden = False
>>>> Next cell
>>>> End With
>>>> stoppit:
>>>> Application.EnableEvents = True
>>>> End Sub
>>>>
>>>> With the macros, this runs slower than desired. Is there a way to keep
>>>> it in VB and still allow a way to use the hide / show buttons, so it's
>>>> not automatic? It runs much faster thru VB.
>>>>
>>>> Thanks,
>>>> Dan
>>>>
>>>>
>>>
>>
>>
>
 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      6th Aug 2008
Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Dan Brimley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Great, that will work. Thanks for your help!
>
>
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> If you want to filter out <0 and 0 just change
>> :="<>0"
>> to
>> :=">0"
>>
>> Of course you can still manually use the filter. To manually unfilter
>> data>filter>autofilter
>> or put the last line in a separate macro
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Dan Brimley" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Thanks. Does this give me the ability to manually filter or unfilter
>>> the zeros? How would I do that? Also, if a value is less than zero, I
>>> don't want to filter it. I forgot to mention that in my original post.
>>>
>>> Thanks
>>>
>>>
>>>
>>> "Don Guillett" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> Filtering should be faster.
>>>>
>>>> Sub hidezero()
>>>> Lr = Cells(Rows.Count, "a").End(xlUp).Row
>>>> Range("A1:a" & Lr).AutoFilter Field:=1, Criteria1:="<>0"
>>>> 'Range("A1:a" & Lr).AutoFilter' to unfilter
>>>> End Sub
>>>>
>>>> --
>>>> Don Guillett
>>>> Microsoft MVP Excel
>>>> SalesAid Software
>>>> (E-Mail Removed)
>>>> "Dan Brimley" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> Hi,
>>>>>
>>>>> I used this code in VB to automatically hide rows that have formulas
>>>>> resulting in a value of zero:
>>>>>
>>>>> Private Sub Worksheet_Calculate()
>>>>> On Error GoTo stoppit
>>>>> Application.EnableEvents = False
>>>>> With ActiveSheet.UsedRange
>>>>> .Rows.Hidden = False
>>>>> For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
>>>>> If cell.Text = "" Or cell.Value = 0 Then _
>>>>> cell.EntireRow.Hidden = True
>>>>> Next cell
>>>>> End With
>>>>> stoppit:
>>>>> Application.EnableEvents = True
>>>>> End Sub
>>>>>
>>>>> Then I decided that I would rather have it so I can hide or unhide
>>>>> manually, so I created macros with hide and show buttons with this
>>>>> code:
>>>>> Sub Hide()
>>>>> On Error GoTo stoppit
>>>>> Application.EnableEvents = False
>>>>> With ActiveSheet.UsedRange
>>>>> .Rows.Hidden = False
>>>>> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
>>>>> If cell.Text = "" Or cell.Value = 0 Then _
>>>>> cell.EntireRow.Hidden = True
>>>>> Next cell
>>>>> End With
>>>>> stoppit:
>>>>> Application.EnableEvents = True
>>>>> End Sub
>>>>> ------------------------------
>>>>> Sub Show()
>>>>> On Error GoTo stoppit
>>>>> Application.EnableEvents = False
>>>>> With ActiveSheet.UsedRange
>>>>> .Rows.Hidden = False
>>>>> For Each cell In .Columns(30).SpecialCells(xlCellTypeFormulas)
>>>>> If cell.Text = "" Or cell.Value = 0 Then _
>>>>> cell.EntireRow.Hidden = False
>>>>> Next cell
>>>>> End With
>>>>> stoppit:
>>>>> Application.EnableEvents = True
>>>>> End Sub
>>>>>
>>>>> With the macros, this runs slower than desired. Is there a way to
>>>>> keep it in VB and still allow a way to use the hide / show buttons, so
>>>>> it's not automatic? It runs much faster thru VB.
>>>>>
>>>>> Thanks,
>>>>> Dan
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
 
Reply With Quote
 
 
 
Reply

« copy and paste challenge | compare rows »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Faster code to quickly hide several rows? Sabine Microsoft Excel Programming 7 25th Sep 2009 02:09 PM
How to make a few rows visible and hide some others ... faster tskogstrom Microsoft Excel Programming 4 6th Oct 2007 07:05 AM
Automatically Hide rows with a value of zero tim@beacondevelopment.com Microsoft Excel Worksheet Functions 2 20th Jun 2007 08:13 PM
faster way to hide rows w/o using autofilter? barbetta3141@yahoo.com Microsoft Excel Programming 3 25th May 2006 05:39 PM
Hide rows automatically =?Utf-8?B?a2lt?= Microsoft Excel Misc 1 14th Sep 2005 02:23 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 11:15 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top