Finding last row when there are more than 65536 rows

Question

  ws1.Cells(1, i).Copy ws2.Range("a65536").End(xlUp).Offset(1, 0)

I am looping through the sheet so i is a part of loop. The number of rows are more than 65536 , then how can I adjust this. Thanks in advance.

Has been asked many times before. `xlUp` as below will give the right answer normally, a blank column being one edge condition where it won't. `Find` is better — brettdj, Mar 31 '15 at 07:02

score 3 · Accepted Answer · answered Mar 31 '15 at 05:00

3

You should use a count of rows rather than a hard coded value.

With ws2
    ws1.Cells(1, i).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

answered Mar 31 '15 at 05:00

chris neilsen

52,446
10
84
123

Using `CountLarge` instead of `Count` may be a good idea in some instances too? I don't think the current number of rows cause an overflow error, but it is a problem I've had when counting cells in the past... – eirikdaude Mar 31 '15 at 06:16
1

@eirikdaude if the returned result _might_ exceed the max of a Long, then use CountLarge. Not an issue when counting rows in a single column ( at least with the current sheet limits). You would have to count cells in >2000 columns to need to use CountLong currently. – chris neilsen Mar 31 '15 at 07:14

tobriand · Answer 2 · 2015-03-31T07:44:49.083

1

Simplest approach is wks.usedrange.rows.count. Reasonably sure the bugs related to usedrange updating when saving the workbook were fixed in post-excel...

edited Mar 31 '15 at 07:44

answered Mar 31 '15 at 07:15

tobriand

1,095
15
29

score 1 · Answer 3 · answered Aug 21 '15 at 04:50

All concise solutions have cases where they fail. The accepted answer will produce the wrong result in the circumstance where the column is completely blank.

Here is a different way that works as long as there are no blanks above or in-between the data:

ws1.Cells(, i).Copy ws2.[INDEX(A:A,COUNTA(A:A)+1)]

score 0 · Answer 4 · answered Mar 31 '15 at 17:36

Remember that you can always get the last used range on a excel worksheet as follow:

Dim lastRange as Range
Dim lastColumn as Long
Dim lastRow as Long

Set lastRange = Worksheets(?).UsedRange.SpecialCells(xlCellTypeLastCell)

lastColumn = lastRange.Column 'return the last column index
lastRow = lastRange.Row 'return the last row index

So to answer your question, you can use my previous example like this:

 ws1.Cells(1, i).Copy ws2.Range("A" & lastRow).Offset(1, 0) 'or
 ws1.Cells(1, i).Copy ws2.Cells(lastRow, 1).Offset(1, 0)

Both statements will do the job for you. Just be conscious that you have to redefine the lastRange Object and the lastRow variable within the loop, otherwise the lastRange variable will hold the value of its initialization.

Good Luck!

Andrés Alejandro García Hurtado.

Finding last row when there are more than 65536 rows

4 Answers4