Problem with move more thab 65536

[parscon]

Hello ,
I have a problem with row more than A65536 , I want to edit my below code like Range("A" & Rows.Count).End(xlUp)) but it does not work.
i have about 955000 row . Could you please help me to modify the below VBA .

Thank you for your help.

Sub moving() 
    Dim idx As Long 
     
    For Each d In Worksheets("Sheet1").Range("A1:A65536") 
         
        idx = 0 
        On Error Resume Next 
        idx = Application.Match(d.Value, Worksheets("Sheet2").Columns("A"), 0) 
        On Error Goto 0 
        If idx > 0 Then 
             
            Worksheets("Sheet2").Cells(idx, 1).Resize(1, 5).Copy _
            Worksheets("Sheet3").Cells(Worksheets("Sheet3").Rows.Count, "A").End(xlUp).Offset(1, 0) 
        End If 
    Next d 
End Sub

[ashleyuk1984]

Row 65536 was the limit on the older Excel versions.... This seems like a strange problem to have if you are certain that you have 955000 (more than 65536 rows)... Are you sure your on the newer versions of Excel?

[parscon]

Yes , I use version 2013. , so in new version it will not get any problem ?

[westconn1]

did you edit the working range?

For Each d In Worksheets("Sheet1").Range("A1:A65536")

try

For Each d In Worksheets("Sheet1").Range("A:A")

else find the last used row

[parscon]

Thank you very much but it will be very slow .

[westconn1]

it will be very slow .

which is why i suggested finding the last row of data, or exit on empty cell

it may be quicker to add all matched cells /range to a non contiguous range then copy the whole range to workbook 3 in one go
it would be marginally quicker to only find the next row in sheet 3 once, before the loop then +1 after copying each time

[parscon]

Could you please provide me some code .

Thank you

[Bob Phillips]

Sub moving() 
    Dim idx As Long 
     
    For Each d In Worksheets("Sheet1").UsedRange.Columns("A")
         
        idx = 0 
        On Error Resume Next 
        idx = Application.Match(d.Value, Worksheets("Sheet2").Columns("A"), 0) 
        On Error Goto 0 
        If idx > 0 Then 
             
            Worksheets("Sheet2").Cells(idx, 1).Resize(1, 5).Copy _ 
            Worksheets("Sheet3").Cells(Worksheets("Sheet3").Rows.Count, "A").End(xlUp).Offset(1, 0) 
        End If 
    Next d 
End Sub

[parscon]

Thank you very much xld.