Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
i have to loop through some rows and test to see which rows to hide. if i hide
the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
Switch off ScreenUpdating and AutoCalculation to make it much faster.
I was able to hide 500 rows in no time - try it this way: Sub HideRows() Dim j As Integer Application.ScreenUpdating = False ' makes it much faster Application.Calculation = xlManual For j = 6 To 510 Worksheets("ListX").Range("A" & j).EntireRow.Hidden = True Next j Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub "Gary Keramidas" wrote: i have to loop through some rows and test to see which rows to hide. if i hide the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
that's not the problem, those are already off.
-- Gary "Franz Erhart" wrote in message ... Switch off ScreenUpdating and AutoCalculation to make it much faster. I was able to hide 500 rows in no time - try it this way: Sub HideRows() Dim j As Integer Application.ScreenUpdating = False ' makes it much faster Application.Calculation = xlManual For j = 6 To 510 Worksheets("ListX").Range("A" & j).EntireRow.Hidden = True Next j Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub "Gary Keramidas" wrote: i have to loop through some rows and test to see which rows to hide. if i hide the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
Why don't you build up a range of the rows as you find a match, and the hide
the range at the end With ws For x = 5 To lastrow If cell_meets_condition Then If rng Is Nothing Then Set rng = Rows(x) Else Set rng = Union(rng, Rows(x)) End If End If Nex t If Not rng Is Nothing Then rng.Hidden = True End With or better, filter by your condition anhd delete visible rows. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... that's not the problem, those are already off. -- Gary "Franz Erhart" wrote in message ... Switch off ScreenUpdating and AutoCalculation to make it much faster. I was able to hide 500 rows in no time - try it this way: Sub HideRows() Dim j As Integer Application.ScreenUpdating = False ' makes it much faster Application.Calculation = xlManual For j = 6 To 510 Worksheets("ListX").Range("A" & j).EntireRow.Hidden = True Next j Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub "Gary Keramidas" wrote: i have to loop through some rows and test to see which rows to hide. if i hide the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
thanks bob, i'll give those a try.
-- Gary "Bob Phillips" wrote in message ... Why don't you build up a range of the rows as you find a match, and the hide the range at the end With ws For x = 5 To lastrow If cell_meets_condition Then If rng Is Nothing Then Set rng = Rows(x) Else Set rng = Union(rng, Rows(x)) End If End If Nex t If Not rng Is Nothing Then rng.Hidden = True End With or better, filter by your condition anhd delete visible rows. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... that's not the problem, those are already off. -- Gary "Franz Erhart" wrote in message ... Switch off ScreenUpdating and AutoCalculation to make it much faster. I was able to hide 500 rows in no time - try it this way: Sub HideRows() Dim j As Integer Application.ScreenUpdating = False ' makes it much faster Application.Calculation = xlManual For j = 6 To 510 Worksheets("ListX").Range("A" & j).EntireRow.Hidden = True Next j Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub "Gary Keramidas" wrote: i have to loop through some rows and test to see which rows to hide. if i hide the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
Hi Gary
Very interesting. I found though that I had to make some amendments before it would run and I have annotated the code to show this. I wonder did you reset the array base to Option 1 perhaps? I also found I had to reduce the frequency of hides from 50 to 40 if ranges greater than a 1000 were used and is clearly something to do with the limit of characters in the range as you had mentioned. Looking at the code it would appear at first sight as though groups of rows are hidden in multiples of the mod number but that does not seem to be the case. In the interest of performing less block hides I have included a debug.print to show this. To speed things up a little I have taken out the branch where the array is incremented and delimiter added. This will of course add a delimeter at the start of the string but that is removed just once before the hide statement. Your code would also equally serve well to delete rows. In my experiments I found the same character limitation on Bob Philips suggestion though I have not taken that further. I'd be grateful of your comments. Geoff K Sub TestHideRows() Dim x As Long Dim lastrow As Long Dim arr() Dim cAddr As String Dim j As Long '''as a given '''Application.ScreenUpdating = False '''Application.Calculation = xlManual With Sheets(1) lastrow = .Cells.Find("*", .Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious).Row cAddr = "" For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 Then 'do nothing Else 'if both are zero j = j + 1 ReDim Preserve arr(0 To x - 5) '<<< changed from 1 to 0 arr(x - 5) = .Range("A" & x).Address(0, 0) cAddr = cAddr & "," & arr(x - 5) If UBound(arr) Mod 40 = 0 Then '<<< changed from 50 to 40 cAddr = Right(cAddr, Len(cAddr) - 1) Debug.Print j, cAddr .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(0 To 1) '<<< changed from 1 to 0 End If End If Next cAddr = Right(cAddr, Len(cAddr) - 1) Debug.Print j, cAddr .Range(cAddr).EntireRow.Hidden = True End With End Sub "Gary Keramidas" wrote: i have to loop through some rows and test to see which rows to hide. if i hide the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
A follow up:
On further experimentation I'm not sure what purpose the array is serving. This seems quicker and from a range of 2000 rows with 1800 assorted addresses for hiding it took 0.23 seconds to complete. Perhaps a way of improving the performance further would be to reduce the impact of the string length limitation by combining addresses viz "A6:A25,A40,A67:A79" etc Geoff K Sub Testit3() Dim x As Long Dim lastrow As Long Dim cAddr As String Application.ScreenUpdating = False Application.Calculation = xlManual With Sheets(1) lastrow = .Cells.Find("*", .Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious).Row cAddr = "" For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 Then 'do nothing Else cAddr = cAddr & "," & .Range("A" & x).Address(0, 0) If Len(cAddr) 240 Then cAddr = Right(cAddr, Len(cAddr) - 1) .Range(cAddr).EntireRow.Hidden = True cAddr = "" End If End If Next If Len(cAddr) 0 Then cAddr = Right(cAddr, Len(cAddr) - 1) .Range(cAddr).EntireRow.Hidden = True End If End With Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub "Geoff K" wrote: Hi Gary Very interesting. I found though that I had to make some amendments before it would run and I have annotated the code to show this. I wonder did you reset the array base to Option 1 perhaps? I also found I had to reduce the frequency of hides from 50 to 40 if ranges greater than a 1000 were used and is clearly something to do with the limit of characters in the range as you had mentioned. Looking at the code it would appear at first sight as though groups of rows are hidden in multiples of the mod number but that does not seem to be the case. In the interest of performing less block hides I have included a debug.print to show this. To speed things up a little I have taken out the branch where the array is incremented and delimiter added. This will of course add a delimeter at the start of the string but that is removed just once before the hide statement. Your code would also equally serve well to delete rows. In my experiments I found the same character limitation on Bob Philips suggestion though I have not taken that further. I'd be grateful of your comments. Geoff K Sub TestHideRows() Dim x As Long Dim lastrow As Long Dim arr() Dim cAddr As String Dim j As Long '''as a given '''Application.ScreenUpdating = False '''Application.Calculation = xlManual With Sheets(1) lastrow = .Cells.Find("*", .Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious).Row cAddr = "" For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 Then 'do nothing Else 'if both are zero j = j + 1 ReDim Preserve arr(0 To x - 5) '<<< changed from 1 to 0 arr(x - 5) = .Range("A" & x).Address(0, 0) cAddr = cAddr & "," & arr(x - 5) If UBound(arr) Mod 40 = 0 Then '<<< changed from 50 to 40 cAddr = Right(cAddr, Len(cAddr) - 1) Debug.Print j, cAddr .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(0 To 1) '<<< changed from 1 to 0 End If End If Next cAddr = Right(cAddr, Len(cAddr) - 1) Debug.Print j, cAddr .Range(cAddr).EntireRow.Hidden = True End With End Sub "Gary Keramidas" wrote: i have to loop through some rows and test to see which rows to hide. if i hide the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
bob:
seems to work fine, just had to fix this: If Not rng Is Nothing Then rng.EntireRow.Hidden = True -- Gary "Bob Phillips" wrote in message ... Why don't you build up a range of the rows as you find a match, and the hide the range at the end With ws For x = 5 To lastrow If cell_meets_condition Then If rng Is Nothing Then Set rng = Rows(x) Else Set rng = Union(rng, Rows(x)) End If End If Nex t If Not rng Is Nothing Then rng.Hidden = True End With or better, filter by your condition anhd delete visible rows. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... that's not the problem, those are already off. -- Gary "Franz Erhart" wrote in message ... Switch off ScreenUpdating and AutoCalculation to make it much faster. I was able to hide 500 rows in no time - try it this way: Sub HideRows() Dim j As Integer Application.ScreenUpdating = False ' makes it much faster Application.Calculation = xlManual For j = 6 To 510 Worksheets("ListX").Range("A" & j).EntireRow.Hidden = True Next j Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub "Gary Keramidas" wrote: i have to loop through some rows and test to see which rows to hide. if i hide the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
Gary,
That shouldn't have been necessary if you were adding whole rows to the range as you encountered the condition . -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... bob: seems to work fine, just had to fix this: If Not rng Is Nothing Then rng.EntireRow.Hidden = True -- Gary "Bob Phillips" wrote in message ... Why don't you build up a range of the rows as you find a match, and the hide the range at the end With ws For x = 5 To lastrow If cell_meets_condition Then If rng Is Nothing Then Set rng = Rows(x) Else Set rng = Union(rng, Rows(x)) End If End If Nex t If Not rng Is Nothing Then rng.Hidden = True End With or better, filter by your condition anhd delete visible rows. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... that's not the problem, those are already off. -- Gary "Franz Erhart" wrote in message ... Switch off ScreenUpdating and AutoCalculation to make it much faster. I was able to hide 500 rows in no time - try it this way: Sub HideRows() Dim j As Integer Application.ScreenUpdating = False ' makes it much faster Application.Calculation = xlManual For j = 6 To 510 Worksheets("ListX").Range("A" & j).EntireRow.Hidden = True Next j Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub "Gary Keramidas" wrote: i have to loop through some rows and test to see which rows to hide. if i hide the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on hiding rows
popped an error until i did that. unable to set the hidden property of the range
class. here's what rng contains: ?rng.Address $6:$16,$18:$39,$41:$68,$70:$95,$97:$100,$102:$107, $109:$144,$146:$170 -- Gary "Bob Phillips" wrote in message ... Gary, That shouldn't have been necessary if you were adding whole rows to the range as you encountered the condition . -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... bob: seems to work fine, just had to fix this: If Not rng Is Nothing Then rng.EntireRow.Hidden = True -- Gary "Bob Phillips" wrote in message ... Why don't you build up a range of the rows as you find a match, and the hide the range at the end With ws For x = 5 To lastrow If cell_meets_condition Then If rng Is Nothing Then Set rng = Rows(x) Else Set rng = Union(rng, Rows(x)) End If End If Nex t If Not rng Is Nothing Then rng.Hidden = True End With or better, filter by your condition anhd delete visible rows. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... that's not the problem, those are already off. -- Gary "Franz Erhart" wrote in message ... Switch off ScreenUpdating and AutoCalculation to make it much faster. I was able to hide 500 rows in no time - try it this way: Sub HideRows() Dim j As Integer Application.ScreenUpdating = False ' makes it much faster Application.Calculation = xlManual For j = 6 To 510 Worksheets("ListX").Range("A" & j).EntireRow.Hidden = True Next j Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub "Gary Keramidas" wrote: i have to loop through some rows and test to see which rows to hide. if i hide the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a better way. as i loop, i create and array with a cell address of which rows i want to hide. the only problem is, if the string is more than 256 characters, it won't work: if this range contained more than 256 characters it wouldn't work range("A6,A9,A12,A15").entirerow.hidden = true so i wrote the code below and was wondering if there was a better way. this code runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1 at a time. it basically adds the cell address to a string until there are 50 address, then it hides 50 rows at a time and resets the array. then what's less than 50 at the end gets hidden when the loop exits. data starts at row 5: With ws For x = 5 To lastrow If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _ Then 'do nothing Else ReDim Preserve arr(1 To x - 5) arr(x - 5) = .Range("A" & x).Address(0, 0) If cAddr = "" Then cAddr = arr(x - 5) Else cAddr = cAddr & "," & arr(x - 5) End If If UBound(arr) Mod 50 = 0 Then .Range(cAddr).EntireRow.Hidden = True cAddr = "" ReDim arr(1 To 1) End If End If Next ws.Range(cAddr).EntireRow.Hidden = True End With -- Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic VBA question - hiding rows | Excel Discussion (Misc queries) | |||
Color alternate rows when after hiding selected rows | Excel Worksheet Functions | |||
Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
hiding Rows and buttons/comboxes, over the rows | Excel Programming |