Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Performance Hiding Rows
I have a loop hiding blank rows. It just seems to run very slowly.
What can I do to increase the speed of this code? Private Sub Worksheet_Activate() Dim rng As Range Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown)) Hide_Rows rng End Sub Public Sub Hide_Rows(rng) Application.ScreenUpdating = False Dim r As Range Dim row As Long rng.Select For Each r In Selection r.Select row = ActiveCell.row Application.StatusBar = "Row " & row If IsEmpty(r) Or r = "None" Then Selection.EntireRow.Hidden = True Else Selection.EntireRow.Hidden = False End If Next r Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks, Brent |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Performance Hiding Rows
Brent,
This is a little simpler and should run much faster. It works from the bottom up, turns off the display of page breaks and doesn't select anything. '----------------------- Public Sub Hide_Rows(ByRef rng As Excel.Range) Dim r As Long Application.ScreenUpdating = False For r = rng.Rows.Count To 1 Step -1 Application.StatusBar = "Row " & r ActiveSheet.DisplayPageBreaks = False If Len(rng(r).Value) = 0 Or rng(r).Value = "None" Then rng(r).EntireRow.Hidden = True Else rng(r).EntireRow.Hidden = False End If Next r Set rng = Nothing Application.StatusBar = False Application.ScreenUpdating = True End Sub '------------------------ Jim Cone San Francisco, USA "Fid" wrote in message oups.com... I have a loop hiding blank rows. It just seems to run very slowly. What can I do to increase the speed of this code? Private Sub Worksheet_Activate() Dim rng As Range Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown)) Hide_Rows rng End Sub Public Sub Hide_Rows(rng) Application.ScreenUpdating = False Dim r As Range Dim row As Long rng.Select For Each r In Selection r.Select row = ActiveCell.row Application.StatusBar = "Row " & row If IsEmpty(r) Or r = "None" Then Selection.EntireRow.Hidden = True Else Selection.EntireRow.Hidden = False End If Next r Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks, Brent |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Performance Hiding Rows
Public Sub Hide_Rows(rng)
Application.ScreenUpdating = False Dim r As Range rng.EntireRow.Hidden = False 'rng.Select For Each r In rng 'r.Select 'row = ActiveCell.row if r.row mod 20 = 0 then Application.StatusBar = "Row " & r.row End if If IsEmpty(r) Or r = "None" Then r.EntireRow.Hidden = True End If Next r Application.StatusBar = False Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "Fid" wrote in message oups.com... I have a loop hiding blank rows. It just seems to run very slowly. What can I do to increase the speed of this code? Private Sub Worksheet_Activate() Dim rng As Range Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown)) Hide_Rows rng End Sub Public Sub Hide_Rows(rng) Application.ScreenUpdating = False Dim r As Range Dim row As Long rng.Select For Each r In Selection r.Select row = ActiveCell.row Application.StatusBar = "Row " & row If IsEmpty(r) Or r = "None" Then Selection.EntireRow.Hidden = True Else Selection.EntireRow.Hidden = False End If Next r Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks, Brent |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Performance Hiding Rows
First, your selection can in some circumstances extend to the end of the
sheet (such as ALL cells below the specified cell are blank). I would use this to avoid that problem: Set rng = Range(Cells(6, 7), Columns(7).SpecialCells (xlCellTypeLastCell)) Then, (minor point) no need to select r; just use row = r.Row and r.EntireRow.Hidden. Also, since you use r repeatedly, using With r... End With will be faster. Those are the obvious things I can see that might help. The first one is the main one: if your code had to loop through over 65000 rows and check and hide them all, well, I bet it was indeed pretty slow! "Fid" wrote: I have a loop hiding blank rows. It just seems to run very slowly. What can I do to increase the speed of this code? Private Sub Worksheet_Activate() Dim rng As Range Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown)) Hide_Rows rng End Sub Public Sub Hide_Rows(rng) Application.ScreenUpdating = False Dim r As Range Dim row As Long rng.Select For Each r In Selection r.Select row = ActiveCell.row Application.StatusBar = "Row " & row If IsEmpty(r) Or r = "None" Then Selection.EntireRow.Hidden = True Else Selection.EntireRow.Hidden = False End If Next r Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks, Brent |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Performance Hiding Rows
Might want to move the DisplayPageBreaks command up above the loop - good
suggestion. -- Regards, Tom Ogilvy "Jim Cone" wrote in message ... Brent, This is a little simpler and should run much faster. It works from the bottom up, turns off the display of page breaks and doesn't select anything. '----------------------- Public Sub Hide_Rows(ByRef rng As Excel.Range) Dim r As Long Application.ScreenUpdating = False For r = rng.Rows.Count To 1 Step -1 Application.StatusBar = "Row " & r ActiveSheet.DisplayPageBreaks = False If Len(rng(r).Value) = 0 Or rng(r).Value = "None" Then rng(r).EntireRow.Hidden = True Else rng(r).EntireRow.Hidden = False End If Next r Set rng = Nothing Application.StatusBar = False Application.ScreenUpdating = True End Sub '------------------------ Jim Cone San Francisco, USA "Fid" wrote in message oups.com... I have a loop hiding blank rows. It just seems to run very slowly. What can I do to increase the speed of this code? Private Sub Worksheet_Activate() Dim rng As Range Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown)) Hide_Rows rng End Sub Public Sub Hide_Rows(rng) Application.ScreenUpdating = False Dim r As Range Dim row As Long rng.Select For Each r In Selection r.Select row = ActiveCell.row Application.StatusBar = "Row " & row If IsEmpty(r) Or r = "None" Then Selection.EntireRow.Hidden = True Else Selection.EntireRow.Hidden = False End If Next r Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks, Brent |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Performance Hiding Rows
Tom,
I agree, DisplayPageBreaks does belong above the loop. Regards, Jim Cone "Tom Ogilvy" wrote in message ... Might want to move the DisplayPageBreaks command up above the loop - good suggestion. Regards, Tom Ogilvy "Jim Cone" wrote in message ... Brent, This is a little simpler and should run much faster. It works from the bottom up, turns off the display of page breaks and doesn't select anything. '----------------------- Public Sub Hide_Rows(ByRef rng As Excel.Range) Dim r As Long Application.ScreenUpdating = False For r = rng.Rows.Count To 1 Step -1 Application.StatusBar = "Row " & r ActiveSheet.DisplayPageBreaks = False If Len(rng(r).Value) = 0 Or rng(r).Value = "None" Then rng(r).EntireRow.Hidden = True Else rng(r).EntireRow.Hidden = False End If Next r Set rng = Nothing Application.StatusBar = False Application.ScreenUpdating = True End Sub '------------------------ Jim Cone San Francisco, USA |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Performance Hiding Rows
Iterate through the UsedRange as before then use this to hide all rows after the UsedRange: rng = Me.UsedRange.Rows(Me.UsedRange.Rows.Count).Row + 1 & ":" & 65536 Me.Range(rng).RowHeight = 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow performance after print preview | Excel Discussion (Misc queries) | |||
Excel performance running slow | Excel Discussion (Misc queries) | |||
excel file performance slow | Excel Discussion (Misc queries) | |||
Very slow performance while exploring. | Excel Discussion (Misc queries) | |||
Spreadsheet performance is slow | Excel Worksheet Functions |