Speed?
 

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:G220 1,G2203:G2401") '<< adjust
for correct range
Rng(1, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng

End Sub

Any ideas appreciated.
---
Stu

Speed?
 

Instead of reposting the same article as yesterday, why don't you
tell us what you didn't like about the responses you got. In
particular, this one works in less than a second for me:

http://google.com/groups?threadm=jem...71617102003%40
msnews.microsoft.com

In article ,
"Stu" wrote:

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:G220 1,G2203:G2401") '<< adjust
for correct range
Rng(1, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng

End Sub

Any ideas appreciated.
---
Stu

Speed?
 

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").SpecialCell s( _
xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0

any reason why?

--
Stu


"J.E. McGimpsey" wrote in message
...
Instead of reposting the same article as yesterday, why don't you
tell us what you didn't like about the responses you got. In
particular, this one works in less than a second for me:

http://google.com/groups?threadm=jem...71617102003%40
msnews.microsoft.com

In article ,
"Stu" wrote:

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:G220 1,G2203:G2401") '<<
adjust
for correct range
Rng(1, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng

End Sub

Any ideas appreciated.
---
Stu

Speed?
 

And
when I go on to:
http://google.com/groups?threadm=jem...71617102003%40

I get this message "Unable to find thread. Please recheck the URL."

--

Stu

----

"J.E. McGimpsey" wrote in message
...
Instead of reposting the same article as yesterday, why don't you
tell us what you didn't like about the responses you got. In
particular, this one works in less than a second for me:

http://google.com/groups?threadm=jem...71617102003%40
msnews.microsoft.com

In article ,
"Stu" wrote:

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:G220 1,G2203:G2401") '<<
adjust
for correct range
Rng(1, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng

End Sub

Any ideas appreciated.
---
Stu

Speed?
 

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.)

Stu wrote:

And
when I go on to:
http://google.com/groups?threadm=jem...71617102003%40

I get this message "Unable to find thread. Please recheck the URL."

--

Stu

----

"J.E. McGimpsey" wrote in message
...
Instead of reposting the same article as yesterday, why don't you
tell us what you didn't like about the responses you got. In
particular, this one works in less than a second for me:

http://google.com/groups?threadm=jem...71617102003%40
msnews.microsoft.com

In article ,
"Stu" wrote:

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:G220 1,G2203:G2401") '<<
adjust
for correct range
Rng(1, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng

End Sub

Any ideas appreciated.
---
Stu




--

Dave Peterson

Speed?
 

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





Stu wrote in message
...
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").SpecialCell s( _
xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0

any reason why?

--
Stu


"J.E. McGimpsey" wrote in message
...
Instead of reposting the same article as yesterday, why don't you
tell us what you didn't like about the responses you got. In
particular, this one works in less than a second for me:

http://google.com/groups?threadm=jem...71617102003%40
msnews.microsoft.com

In article ,
"Stu" wrote:

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:G220 1,G2203:G2401") '<<
adjust
for correct range
Rng(1, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng

End Sub

Any ideas appreciated.
---
Stu

Speed?
 

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.Hid den = False
Application.ScreenUpdating = True


In article ,
"Stu" wrote:

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").SpecialCell s( _
xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0

any reason why?

Speed?
 

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.Hid den = 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
--
Stu




"J.E. McGimpsey" wrote in message
...
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.Hid den = False
Application.ScreenUpdating = True


In article ,
"Stu" wrote:

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").SpecialCell s( _
xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0

any reason why?

Speed?
 

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


In article ,
"Stu" wrote:

how come it is so fast compared to the one I had?

Speed?
 

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

--
Regards,
Tom Ogilvy
Tom Ogilvy wrote in message
...
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





Stu wrote in message
...
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").SpecialCell s( _
xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0

any reason why?

--
Stu


"J.E. McGimpsey" wrote in message
...
Instead of reposting the same article as yesterday, why don't you
tell us what you didn't like about the responses you got. In
particular, this one works in less than a second for me:

http://google.com/groups?threadm=jem...71617102003%40
msnews.microsoft.com

In article ,
"Stu" wrote:

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:G220 1,G2203:G2401")
'<<
adjust
for correct range
Rng(1, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng

End Sub

Any ideas appreciated.
---
Stu

Speed?
 

Ok,
That is clever.

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

Thanks
--
Stu



"J.E. McGimpsey" wrote in message
...
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


In article ,
"Stu" wrote:

how come it is so fast compared to the one I had?

Speed?
 

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Stu" wrote in message ...
can you do anything with my other speeding up code?
The thread name is: Another Speed Problem.
=