Speed?

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

Hi,
Is there an way to speed up a macro, I have the following macro and it takes
a bit of time to run and I want it to run fast if that is possible:

Sub Main()

Dim Rng As Range
For Each Rng In
Range("G3:G201,G203:G401,G403:G601,G603:G801,G803:G1001,G1003:G1201,G1203:G1
401,G1403:G1601,G1603:G1801,G1803:G2001,G2003:G2201,G2203:G2401") '<< adjust
for correct range
Rng(1, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng

End Sub

Any ideas appreciated.
 
Well, this did nothing when I ran it:

On Error Resume Next 'in case no blanks
Range("G3:G201,G203:G401,G403:G601," & _
"G603:G801,G803:G1001,G1003:G1201," & _
"G1203:G1401,G1403:G1601,G1603:G1801," & _
"G1803:G2001,G2003:G2201,G2203:G2401").SpecialCells( _
xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0

any reason why?
 
There was a line wrap problem. The original URL spanned two lines. Put it all
together and you'll get there (I did).

But you already found it (since you posted it in your reply.)

Are you sure you have empty cells in that range? Not formulas that evaluate to
"". They're not empty--they contain a formula. And not formulas that evaluated
to "" that were converted to values. These cells aren't empty either.

(I'm guessing it's one of these. If you post back with more details, you should
get a nice response.)
 
if J.E's code did nothing, then your cells must not be empty - perhaps you
have a formula that returns a "". In that case you could do this

Sub Main()
Dim Rng As Range
Dim Rng1 as Range
For Each Rng In
Range("G3:G201,G203:G401,G403:G601," & _
"G603:G801,G803:G1001,G1003:G1201," & _
"G1203:G1401,G1403:G1601,G1603:G1801," & _
G1803:G2001,G2003:G2201,G2203:G2401")
if len(trim(rng.value)) = 0 then
if rng1 is nothing
set rng1 = rng
else
set rng1 = union(rng1,rng)
end if
Next Rng
if not rng1 is nothing then
rng1.EntireRow.Hidden = True
End if
End Sub

--
Regards,
Tom Ogilvy
 
perhaps your cells aren't truly blank?

When I ran it, it hid any row in the specified range for which the
cell was blank.

Example: I put values in G5:G27 and G2390 to G2402, leaving the rest
of column G blank. When I ran the macro, these rows were then
visible:

1:2,5:27,202,402,602,802,1002,1202,
1402,1602,1802,2002,2202, 2390:65536

This *won't* work if your cells contain formulae (since cells that
contain a formula aren't blank). This should be faster than your
current code in that case:

Dim hideRows As Range
Application.ScreenUpdating = False
With Range("G2:G2401")
.EntireRow.Hidden = False
.AutoFilter Field:=1, Criteria1:="="
On Error Resume Next
Set hideRows = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Selection.AutoFilter
If Not hideRows Is Nothing Then hideRows.EntireRow.Hidden = True
Range("G202,G402,G602,G802,G1002,G1202,G1402," & _
"G1602,G1802,G2002,G2202,G2402").EntireRow.Hidden = False
Application.ScreenUpdating = True
 
I do have formulas in the cells that is true.
This code:

Sub Main()
Dim hideRows As Range
Application.ScreenUpdating = False
With Range("G3:G2401")
.EntireRow.Hidden = False
.AutoFilter Field:=1, Criteria1:="="
On Error Resume Next
Set hideRows = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Selection.AutoFilter
If Not hideRows Is Nothing Then hideRows.EntireRow.Hidden = True
Range("G202,G402,G602,G802,G1002,G1202,G1402," & _
"G1602,G1802,G2002,G2202,G2402").EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub

Does it in the blink of an eye, how come it is so fast compared to the one I
had?

Thanks
 
1) Screenupdating = False, meaning that XL isn't updating the screen
every time a row is hidden or made visible.

2) All the rows are unhidden (.EntireRow.Hidden = False) or hidden
in one step (hideRows.EntireRow.Hidden = True).

3) Instead of a relatively slow VBA loop, Autofilter uses optimized
compiled XL functions
 
Couple of typos:

Sub Main()
Dim Rng As Range
Dim Rng1 As Range
For Each Rng In _
Range("G3:G201,G203:G401,G403:G601," & _
"G603:G801,G803:G1001,G1003:G1201," & _
"G1203:G1401,G1403:G1601,G1603:G1801," & _
"G1803:G2001,G2003:G2201,G2203:G2401")
If Len(Trim(Rng.Value)) = 0 Then
If Rng1 Is Nothing Then
Set Rng1 = Rng
Else
Set Rng1 = Union(Rng1, Rng)
End If
End If
Next Rng
If Not Rng1 Is Nothing Then
Rng1.EntireRow.Hidden = True
End If
End Sub
 
Ok,
That is clever.

can you do anything with my other speeding up code?
The thread name is: Another Speed Problem.

Thanks
 
Hi Stu,
Not really speeding up the code but you can turn off Calculation
besides turning off Screen Updating and generally get a big improvement.
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Make sure that your code does not leave these turned off
if the macro is terminated. Suggest you continue in the other thread
after looking over the above web page.


Stu said:
can you do anything with my other speeding up code?
The thread name is: Another Speed Problem.
Click to expand...
=
 
Back
Top