Macro updating Very slow

[smartguy]

Hello all,

I am using the below code to get data. i am using 1 Lakh data. Macro taking to much to time.

Sub UpdateData()
Application.ScreenUpdating = False
Dim LastRow As Long, desWS As Worksheet, srcWS As Worksheet, fnd As Range, ID As Range
Set desWS = Sheets("Sheet2")
Set srcWS = Sheets("Sheet1")
LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each ID In desWS.Range("A2:A" & LastRow)
Set fnd = srcWS.Range("A:A").Find(ID, LookIn:=xlValues, lookat:=xlWhole)
If Not fnd Is Nothing Then
fnd.Offset(0, 1).Resize(, 9).Copy ID.Offset(0, 1)
End If
Next ID
Application.ScreenUpdating = True
End Sub

 

[Jeffrey Mahoney]

I could be wrong but I think you're looping through all your cells in column A to find each cell value in column A.

So....

It goes along to find Jeff in cell A10, then searches all of column A for Jeff and finds one in row 2. Then copies some stuff in row 2
It goes along to find Jeff in cell A200, then searches all of column A for Jeff and finds one in row 2. Then copies some stuff in row 2
It goes along to find Jeff in cell A310, then searches all of column A for Jeff and finds one in row 2. Then copies some stuff in row 2

I tried editing the code but got lost. Not sure what you're trying to accomplish.

 

[Fluff]

How about

Sub smartguy()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet
    Dim Cl As Range
    
    Set desWS = Sheets("Sheet2")
    Set srcWS = Sheets("pcode")
    With CreateObject("scripting.dictionary")
      For Each Cl In srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 1).Resize(, 9)
      Next Cl
      For Each Cl In desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 1).Resize(, 9) = .Item(Cl.Value)
      Next Cl
   End With
End Sub