how can i do my code by dictionary to make it fast

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010
hi
i need help to make my code fast by dictionary i have about 10000 rows to copy data from main sheet to other sheets based on values of column

VBA Code:
Sub My_Ad_filter()
Dim Rg As Range
Dim Cret_rg As Range
Dim arr, itm
Application.ScreenUpdating = False
arr = Array(sheet1, sheet2, sheet3, sheet4)
Set Rg = Sheets("g").Range("A14").CurrentRegion
For Each itm In arr
  With Sheets(itm & "")
    .Range("A14").CurrentRegion.ClearContents
    .Range("aa1") = "depart"
    .Range("aa2") = itm
     Set Cret_rg = .Range("aa1:aa2")
     Rg.AdvancedFilter 2, Cret_rg, .Range("A14")
     Cret_rg.ClearContents
  End With
Next
Application.ScreenUpdating = True
End Sub

thanks
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
How long does your process take?
How many records are on "g" sheet?
Do you have formulas on sheet "g"?

What are sheet1, 2, 3 and 4, are they variable, constant, can you explain?
arr = Array(sheet1, sheet2, sheet3, sheet4)
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010
first of all i can't give you how long time takes if you have a way to do that please inform me
the second the data in sheet("g") are about 10000 rows and is comparable to increasing
third no formulas in sheet("g) just names ,dates and values
for sheets(1,2,3 4) theses name in my workbook it copy data from sheet("g) to them based on column value in sheet("g)
i hope this help
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,857
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
first of all i can't give you how long time takes if you have a way to do that please inform me
Start of your code
VBA Code:
    Dim t As Double
    t = Timer
End of your code
VBA Code:
MsgBox "Code took " & Format(Timer - t, "0.00 secs")
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010

ADVERTISEMENT

thanks mark this is what i got 0.23
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,857
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
So it ran in under a quarter of a second, so why do you need it speeding up?
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010

ADVERTISEMENT

i feel when run code i have to wait moment to show result i think this value not real i no know if is from my pc
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,857
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Did you put the msgbox line before or after the
VBA Code:
Application.ScreenUpdating = True
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,857
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Put it after and run the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top