Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up Hide rows code
Hello,
I am using the following code to hide certain rows. It is perfect when for 100s of rows but is rather slow when I am using 1000s. "Hide" is in column B for the rows I want to hide. I need the code to be triggered when the sheet is activated (becauseThe "Hide" is often conditional and depends on what is happening somewhere else in the workbook). Code ' all columns and rows need to be visible to start with for the macros to work Cells.EntireRow.Hidden = False Cells.EntireColumn.Hidden = False ' Search Dim FirstCell As Range Dim FoundCell As Range Dim AllCells As Range ' look for the first matching cell Set FirstCell = Columns("B:B").Find("Hide", LookIn:=xlValues) 'initialise AllCells Set AllCells = FirstCell Set FoundCell = FirstCell 'loop until the FirstCell is found again Do Set FoundCell = Columns("B:B").FindNext(After:=FoundCell) Set AllCells = Union(FoundCell, AllCells) If FoundCell.Address = FirstCell.Address Then Exit Do Loop 'select the rows where cells have been found Dim Arg1 As Range Set Arg1 = AllCells.EntireRow Arg1.Select 'hide the rows Selection.EntireRow.Hidden = True Columns("A:B").EntireColumn.Hidden = True any ideas very welcome. THANKS -- caroline |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up Hide rows code
Hiding and unhiding generates calculation events in th emore current versions
of XL. To that end you can turn it off and back on to stop the recalc. Additionally get rid of the selects as you really don't need them. Finally your code will crash if no instance of Hide is found. Try this... Private Sub Worksheet_Activate() Dim FirstCell As String Dim FoundCell As Range Dim AllCells As Range Application.Calculation = xlCalculationManual 'all columns and rows need to be visible to start with for the macros to work Cells.EntireRow.Hidden = False Cells.EntireColumn.Hidden = False ' Search ' look for the first matching cell Set FoundCell = Columns("B:B").Find(What:="Hide", _ LookIn:=xlValues) If Not FoundCell Is Nothing Then 'initialise AllCells Set AllCells = FoundCell FirstCell = FoundCell.Address 'loop until the FirstCell is found again Do Set AllCells = Union(FoundCell, AllCells) Set FoundCell = Columns("B:B").FindNext(FoundCell) Loop Until FoundCell.Address = FirstCell 'hide the rows AllCells.EntireRow.Hidden = True Columns("A:B").EntireColumn.Hidden = True End If Application.Calculation = xlCalculationAutomatic End Sub -- HTH... Jim Thomlinson "caroline" wrote: Hello, I am using the following code to hide certain rows. It is perfect when for 100s of rows but is rather slow when I am using 1000s. "Hide" is in column B for the rows I want to hide. I need the code to be triggered when the sheet is activated (becauseThe "Hide" is often conditional and depends on what is happening somewhere else in the workbook). Code ' all columns and rows need to be visible to start with for the macros to work Cells.EntireRow.Hidden = False Cells.EntireColumn.Hidden = False ' Search Dim FirstCell As Range Dim FoundCell As Range Dim AllCells As Range ' look for the first matching cell Set FirstCell = Columns("B:B").Find("Hide", LookIn:=xlValues) 'initialise AllCells Set AllCells = FirstCell Set FoundCell = FirstCell 'loop until the FirstCell is found again Do Set FoundCell = Columns("B:B").FindNext(After:=FoundCell) Set AllCells = Union(FoundCell, AllCells) If FoundCell.Address = FirstCell.Address Then Exit Do Loop 'select the rows where cells have been found Dim Arg1 As Range Set Arg1 = AllCells.EntireRow Arg1.Select 'hide the rows Selection.EntireRow.Hidden = True Columns("A:B").EntireColumn.Hidden = True any ideas very welcome. THANKS -- caroline |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up Hide rows code
Hi Caroline
Could you use something like Dim ws As Worksheet Dim test As String Application.ScreenUpdating = False Application.Calculation = xlCalculationManual test = "hide" '<=== set to what you want hidden Set ws = ActiveSheet If ws.AutoFilterMode = False Then ws.Range("B1").AutoFilter End If If ws.FilterMode Then ws.ShowAllData Selection.AutoFilter Field:=2, Criteria1:="<" & test Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True -- Regards Roger Govier "caroline" wrote in message ... Hello, I am using the following code to hide certain rows. It is perfect when for 100s of rows but is rather slow when I am using 1000s. "Hide" is in column B for the rows I want to hide. I need the code to be triggered when the sheet is activated (becauseThe "Hide" is often conditional and depends on what is happening somewhere else in the workbook). Code ' all columns and rows need to be visible to start with for the macros to work Cells.EntireRow.Hidden = False Cells.EntireColumn.Hidden = False ' Search Dim FirstCell As Range Dim FoundCell As Range Dim AllCells As Range ' look for the first matching cell Set FirstCell = Columns("B:B").Find("Hide", LookIn:=xlValues) 'initialise AllCells Set AllCells = FirstCell Set FoundCell = FirstCell 'loop until the FirstCell is found again Do Set FoundCell = Columns("B:B").FindNext(After:=FoundCell) Set AllCells = Union(FoundCell, AllCells) If FoundCell.Address = FirstCell.Address Then Exit Do Loop 'select the rows where cells have been found Dim Arg1 As Range Set Arg1 = AllCells.EntireRow Arg1.Select 'hide the rows Selection.EntireRow.Hidden = True Columns("A:B").EntireColumn.Hidden = True any ideas very welcome. THANKS -- caroline |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
Code for Excel 2007 to hide rows based on sum of several rows not ina range | Excel Programming | |||
Modification in the CODE to HIDE rows and columns that start with ZERO (code given) | Excel Programming | |||
Speeding Up Code | Excel Programming |