Other method than Select in VBA

Question

x.Worksheets("Sheet1").Activate
Range("A65536").Select 
ActiveCell.End(xlUp).Select
lastrow = ActiveCell.Row 
Range("A2:A" & lastrow).Copy y.Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 0)
Range("B2:B" & lastrow).Copy y.Worksheets("Sheet1").Range("b65536").End(xlUp).Offset(1, 0)

I have read several places that using select is not recommended for copy pasting, and how can one do it without select?

`Find` is also superior to `xlup` etc which doesn't handle edge cases (empty columns, full columns, or hidden cells). See http://stackoverflow.com/questions/8283797/return-a-range-from-a1-to-the-true-last-used-cell/8283941#8283941 — brettdj, Mar 13 '15 at 13:38

Sobigen · Accepted Answer · 2015-03-13T21:00:40.220

2

As an exact analog to your code:

lastRow = x.worksheets("Sheet1").range("A65536").end(xlup).row

Added because of comment recommending better practices:

with x.worksheets("Sheet1")
    lastRow = .Cells(.Rows.Count, 1).End(xlup).Row
end with

edited Mar 13 '15 at 21:00

answered Mar 12 '15 at 18:31

Sobigen

2,038
15
23

1

-1 for bad practice. What if we're using a 2007+ Version of Office and we have a spreadsheet with **hundreds of thousands** of rows? This would say row 65536 is the last row. – RubberDuck Mar 12 '15 at 20:30
@RubberDuck, good point (1,048,576 to be exact). Added 2nd option. – Sobigen Mar 13 '15 at 21:06
Good show old bean. Duly noted and vote reversed. – RubberDuck Mar 13 '15 at 21:17

Jeanno · Answer 2 · 2015-03-12T18:45:55.187

2

You can set lastRow = x.worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1

You need the first empty cell in the column, no the last one occupied

edited Mar 12 '15 at 18:45

answered Mar 12 '15 at 18:39

Jeanno

2,769
4
23
31

Other method than Select in VBA

2 Answers2