Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > can anybody speed up this macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default can anybody speed up this macro
Hello guys, i have the following macro that works but it goes very
slowly

What it does, is that applies an autofilter on several columns and then
copies the filtered values from column 1 to a new sheet. If you know
that when there is no filtered range (so there are no values that
fulfill the criterium) nothing has to be copied, is it possible to
speed it up with a if autofilter.range is empty then do next or
something??

thankx

Worksheets("General fields").Activate
Selection.AutoFilter field:=8, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a2")
Selection.AutoFilter field:=8



'short call
Worksheets("General fields").Activate
Selection.AutoFilter field:=9, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a98")
Selection.AutoFilter field:=9


'rsu
Worksheets("General fields").Activate
Selection.AutoFilter field:=12, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a26")
Selection.AutoFilter field:=12

'espp
Worksheets("General fields").Activate
Selection.AutoFilter field:=13, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a50")
Selection.AutoFilter field:=13

'f shares
Worksheets("General fields").Activate
Selection.AutoFilter field:=14, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a74")
Selection.AutoFilter field:=14

'top hat
Worksheets("General fields").Activate
Selection.AutoFilter field:=16, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a122")
Selection.AutoFilter field:=16

'pension
Worksheets("General fields").Activate
Selection.AutoFilter field:=17, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a128")
Selection.AutoFilter field:=17

'degroof AM
Worksheets("General fields").Activate
Selection.AutoFilter field:=19, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a133")
Selection.AutoFilter field:=19

'degroof ANM
Worksheets("General fields").Activate
Selection.AutoFilter field:=20, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a140")
Selection.AutoFilter field:=20

'other bank
Worksheets("General fields").Activate
Selection.AutoFilter field:=21, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a147")
Selection.AutoFilter field:=21

'other
Worksheets("General fields").Activate
Selection.AutoFilter field:=23, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a152")
Selection.AutoFilter field:=23

'liabilities
Worksheets("General fields").Activate
Selection.AutoFilter field:=24, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a157")
Selection.AutoFilter field:=24


Worksheets("Temp").Buttons.Delete
Worksheets("General fields").Activate
Selection.AutoFilter field:=6

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default can anybody speed up this macro
something like this (UN tested). I would also change selection to the
range("a2:z2")

Worksheets("General fields").Activate
myarray = Array(8, 9, 12, 13)
For Each i In myarray
'MsgBox i
lastrow=sheets("Temp").cells(.rows.count,"a").end( xlup).row+1
Selection.AutoFilter field:=i, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy _
Worksheets("Temp").cells(lastrow,"a")
Next i

--
Don Guillett
SalesAid Software

"matthias" wrote in message
ups.com...
Hello guys, i have the following macro that works but it goes very
slowly

What it does, is that applies an autofilter on several columns and then
copies the filtered values from column 1 to a new sheet. If you know
that when there is no filtered range (so there are no values that
fulfill the criterium) nothing has to be copied, is it possible to
speed it up with a if autofilter.range is empty then do next or
something??

thankx

Worksheets("General fields").Activate
Selection.AutoFilter field:=8, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a2")
Selection.AutoFilter field:=8



'short call
Worksheets("General fields").Activate
Selection.AutoFilter field:=9, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a98")
Selection.AutoFilter field:=9


'rsu
Worksheets("General fields").Activate
Selection.AutoFilter field:=12, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a26")
Selection.AutoFilter field:=12

'espp
Worksheets("General fields").Activate
Selection.AutoFilter field:=13, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a50")
Selection.AutoFilter field:=13

'f shares
Worksheets("General fields").Activate
Selection.AutoFilter field:=14, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a74")
Selection.AutoFilter field:=14

'top hat
Worksheets("General fields").Activate
Selection.AutoFilter field:=16, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a122")
Selection.AutoFilter field:=16

'pension
Worksheets("General fields").Activate
Selection.AutoFilter field:=17, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a128")
Selection.AutoFilter field:=17

'degroof AM
Worksheets("General fields").Activate
Selection.AutoFilter field:=19, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a133")
Selection.AutoFilter field:=19

'degroof ANM
Worksheets("General fields").Activate
Selection.AutoFilter field:=20, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a140")
Selection.AutoFilter field:=20

'other bank
Worksheets("General fields").Activate
Selection.AutoFilter field:=21, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a147")
Selection.AutoFilter field:=21

'other
Worksheets("General fields").Activate
Selection.AutoFilter field:=23, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a152")
Selection.AutoFilter field:=23

'liabilities
Worksheets("General fields").Activate
Selection.AutoFilter field:=24, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a157")
Selection.AutoFilter field:=24


Worksheets("Temp").Buttons.Delete
Worksheets("General fields").Activate
Selection.AutoFilter field:=6



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default can anybody speed up this macro
you can use
set rng = activesheet.autofilter.Range.columns(8).cells
if application.countA(rng) 1 then ' assume header is not empty
' apply the autofilter and copy
else
' skip this one
end if

--
Regards,
Tom Ogilvy


"matthias" wrote:

Hello guys, i have the following macro that works but it goes very
slowly

What it does, is that applies an autofilter on several columns and then
copies the filtered values from column 1 to a new sheet. If you know
that when there is no filtered range (so there are no values that
fulfill the criterium) nothing has to be copied, is it possible to
speed it up with a if autofilter.range is empty then do next or
something??

thankx

Worksheets("General fields").Activate
Selection.AutoFilter field:=8, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a2")
Selection.AutoFilter field:=8



'short call
Worksheets("General fields").Activate
Selection.AutoFilter field:=9, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a98")
Selection.AutoFilter field:=9


'rsu
Worksheets("General fields").Activate
Selection.AutoFilter field:=12, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a26")
Selection.AutoFilter field:=12

'espp
Worksheets("General fields").Activate
Selection.AutoFilter field:=13, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a50")
Selection.AutoFilter field:=13

'f shares
Worksheets("General fields").Activate
Selection.AutoFilter field:=14, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a74")
Selection.AutoFilter field:=14

'top hat
Worksheets("General fields").Activate
Selection.AutoFilter field:=16, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a122")
Selection.AutoFilter field:=16

'pension
Worksheets("General fields").Activate
Selection.AutoFilter field:=17, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a128")
Selection.AutoFilter field:=17

'degroof AM
Worksheets("General fields").Activate
Selection.AutoFilter field:=19, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a133")
Selection.AutoFilter field:=19

'degroof ANM
Worksheets("General fields").Activate
Selection.AutoFilter field:=20, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a140")
Selection.AutoFilter field:=20

'other bank
Worksheets("General fields").Activate
Selection.AutoFilter field:=21, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a147")
Selection.AutoFilter field:=21

'other
Worksheets("General fields").Activate
Selection.AutoFilter field:=23, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a152")
Selection.AutoFilter field:=23

'liabilities
Worksheets("General fields").Activate
Selection.AutoFilter field:=24, Criteria1:="<"
ActiveSheet.AutoFilter.Range.Columns(1).Copy
Destination:=Worksheets("Temp").Range("a157")
Selection.AutoFilter field:=24


Worksheets("Temp").Buttons.Delete
Worksheets("General fields").Activate
Selection.AutoFilter field:=6


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Speed Up Macro Sean[_15_] Excel Programming 2 September 12th 06 05:02 PM
How to speed up this macro? Ctech Excel Programming 19 October 6th 05 04:28 PM
Macro Speed Don Lloyd Excel Programming 4 July 28th 05 06:02 PM
Speed-up a macro! maca[_3_] Excel Programming 3 July 15th 05 06:40 PM
Using With to speed up macro Wesley[_2_] Excel Programming 2 December 30th 03 10:54 AM


All times are GMT +1. The time now is 04:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement