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.