Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically hide rows with 0 value....faster?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically hide rows with 0 value....faster?
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 datafilterautofilter or put the last line in a separate macro -- Don Guillett Microsoft MVP Excel SalesAid Software "Dan Brimley" wrote in message ... 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" wrote in message ... 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 "Dan Brimley" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically hide rows with 0 value....faster?
Great, that will work. Thanks for your help!
"Don Guillett" wrote in message ... 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 datafilterautofilter or put the last line in a separate macro -- Don Guillett Microsoft MVP Excel SalesAid Software "Dan Brimley" wrote in message ... 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" wrote in message ... 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 "Dan Brimley" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically hide rows with 0 value....faster?
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "Dan Brimley" wrote in message ... Great, that will work. Thanks for your help! "Don Guillett" wrote in message ... 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 datafilterautofilter or put the last line in a separate macro -- Don Guillett Microsoft MVP Excel SalesAid Software "Dan Brimley" wrote in message ... 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" wrote in message ... 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 "Dan Brimley" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically Hide Rows | Excel Worksheet Functions | |||
How to make a few rows visible and hide some others ... faster | Excel Programming | |||
faster way to hide rows w/o using autofilter? | Excel Programming | |||
Hide rows automatically | Excel Discussion (Misc queries) | |||
How do I automatically hide rows | Excel Discussion (Misc queries) |