Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows-Macro or Autofilter
I am trying to determine the best way to hide rows based on a certain
criterial. In sheet 1 of my model, I have a column of cells E63:E102 with formulas that either equal zero, or a value greater than zero. I would like to hide the rows where the value is equal to zero. I have used a macro from other worksheets but It does not seem to be working- not even slowly- in this model. I think maybe my file size is too large? (almost 4M). The macro I have been currently using is, Dim c As Range For Each c In Range("e63", Range("e102").End(xlUp)) If c.value = 0 Then c.EntireRow.Hidden = True Else: c.EntireRow.Hidden = False End If Next c End Sub I am not sure if this is the best one to be using. If my results are slow, should I switch to an autofilter macro. I would like to have it as a macro so I can just refresh the range as numbers get added or deleted with a click of a button. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows-Macro or Autofilter
Hi DJ,
You could increase the speed of your macro by temporarily turning of Excel's events and calculatrions and restoring these settings at the end of your macro. Therefore, perhaps try something like: '================ Private Sub CommandButton1_Click() Dim Rng As Range Dim rCell As Range Dim CalcMode As Long Dim ViewMode As Long Set Rng = Me.Range("E63", Range("E102").End(xlUp)) On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells With rCell rCell.EntireRow.Hidden = .Value = 0 End With Next rCell XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<================ --- Regards. Norman wrote in message ... I am trying to determine the best way to hide rows based on a certain criterial. In sheet 1 of my model, I have a column of cells E63:E102 with formulas that either equal zero, or a value greater than zero. I would like to hide the rows where the value is equal to zero. I have used a macro from other worksheets but It does not seem to be working- not even slowly- in this model. I think maybe my file size is too large? (almost 4M). The macro I have been currently using is, Dim c As Range For Each c In Range("e63", Range("e102").End(xlUp)) If c.value = 0 Then c.EntireRow.Hidden = True Else: c.EntireRow.Hidden = False End If Next c End Sub I am not sure if this is the best one to be using. If my results are slow, should I switch to an autofilter macro. I would like to have it as a macro so I can just refresh the range as numbers get added or deleted with a click of a button. Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows-Macro or Autofilter
Hi DJ,
And better still might be: '================ Private Sub CommandButton1_Click() Dim Rng As Range Dim rCell As Range Dim CalcMode As Long Dim ViewMode As Long Set Rng = Me.Range("E63", Range("E102").End(xlUp)) On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Me.DisplayPageBreaks = False For Each rCell In Rng.Cells With rCell rCell.EntireRow.Hidden = .Value = 0 End With Next rCell XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With ActiveWindow.View = ViewMode End Sub '<<================ --- Regards. Norman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows-Macro or Autofilter
One thing to be aware of in your macro: End(xlDirection) bypasses data in
hidden rows or columns. This modification unhides everything and then re-hides the cells with 0. To help with performance and debugging, I like to build one big range and at the end of the evaluation, hide the rows in a single action. Sub HideStuff() Dim c As Range, rngToHide As Range, rngToLookat As Range, lngCalc As Long Set rngToLookat = Range("E63:E102") rngToLookat.EntireRow.Hidden = False For Each c In rngToLookat.Cells If c.Value = 0 Then If rngToHide Is Nothing Then Set rngToHide = c Else Set rngToHide = Union(rngToHide, c) End If End If Next c If Not rngToHide Is Nothing Then lngCalc = Application.Calculation Application.Calculation = xlCalculationManual rngToHide.EntireRow.Hidden = True Application.Calculation = lngCalc End If End Sub -- Tim Zych www.higherdata.com Compare data in workbooks and find differences with Workbook Compare A free, powerful, flexible Excel utility wrote in message ... I am trying to determine the best way to hide rows based on a certain criterial. In sheet 1 of my model, I have a column of cells E63:E102 with formulas that either equal zero, or a value greater than zero. I would like to hide the rows where the value is equal to zero. I have used a macro from other worksheets but It does not seem to be working- not even slowly- in this model. I think maybe my file size is too large? (almost 4M). The macro I have been currently using is, Dim c As Range For Each c In Range("e63", Range("e102").End(xlUp)) If c.value = 0 Then c.EntireRow.Hidden = True Else: c.EntireRow.Hidden = False End If Next c End Sub I am not sure if this is the best one to be using. If my results are slow, should I switch to an autofilter macro. I would like to have it as a macro so I can just refresh the range as numbers get added or deleted with a click of a button. Any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows-Macro or Autofilter
On May 28, 10:53*pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote:
One thing to be aware of in your macro: End(xlDirection) bypasses data in hidden rows or columns. This modification unhides everything and then re-hides the cells with 0. To help with performance and debugging, I like to build one big range and at the end of the evaluation, hide the rows in a single action. Sub HideStuff() * * Dim c As Range, rngToHide As Range, rngToLookat As Range, lngCalc As Long * * Set rngToLookat = Range("E63:E102") * * rngToLookat.EntireRow.Hidden = False * * For Each c In rngToLookat.Cells * * * * If c.Value = 0 Then * * * * * * If rngToHide Is Nothing Then * * * * * * * * Set rngToHide = c * * * * * * Else * * * * * * * * Set rngToHide = Union(rngToHide, c) * * * * * * End If * * * * End If * * Next c * * If Not rngToHide Is Nothing Then * * * * lngCalc = Application.Calculation * * * * Application.Calculation = xlCalculationManual * * * * rngToHide.EntireRow.Hidden = True * * * * Application.Calculation = lngCalc * * End If End Sub -- Tim Zychwww.higherdata.com Compare data in workbooks and find differences with Workbook Compare A free, powerful, flexible Excel utility wrote in message ... I am trying to determine the best way to hide rows based on a certain criterial. *In sheet 1 of my model, I have a column of cells E63:E102 with formulas that either equal zero, or a value greater than zero. I would like to hide the rows where the value is equal to zero. I have used a macro from other worksheets but It does not seem to be working- not even slowly- in this model. I think maybe my file size is too large? (almost 4M). *The macro I have been currently using is, Dim c * * *As Range * *For Each c In Range("e63", Range("e102").End(xlUp)) * * * *If c.value = 0 Then * * * * * *c.EntireRow.Hidden = True * * * *Else: c.EntireRow.Hidden = False * * * *End If * *Next c End Sub I am not sure if this is the best one to be using. If my results are slow, should I switch to an autofilter macro. I would like to have it as a macro so I can just refresh the range as numbers get added or deleted with a click of a button. Any suggestions?- Hide quoted text - - Show quoted text - Hi Tim, Thank you for the response. It seems to be working well and seems pretty efficient given my file size. Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro for hiding rows | New Users to Excel | |||
Macro for hiding rows | Excel Discussion (Misc queries) | |||
Macro for hiding rows | Excel Discussion (Misc queries) | |||
hiding rows with a macro | Excel Programming | |||
hiding rows using macro | Excel Programming |