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

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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)
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
So it ran in under a quarter of a second, so why do you need it speeding up?
 
Upvote 0
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
 
Upvote 0
Did you put the msgbox line before or after the
VBA Code:
Application.ScreenUpdating = True
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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
Back
Top