Other method than Select in VBA [duplicate]

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 stackoverflow.com/questions/8283797/… — brettdj
– brettdj, Commented Mar 13, 2015 at 13:38

Sobigen · Accepted Answer · 2015-03-13 21:00:40Z

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, 2015 at 21:00

answered Mar 12, 2015 at 18:31

Sobigen

2,17916 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RubberDuck Over a year ago

-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.

Sobigen Over a year ago

@RubberDuck, good point (1,048,576 to be exact). Added 2nd option.

RubberDuck Over a year ago

Good show old bean. Duly noted and vote reversed.

Jeanno · Accepted Answer · 2015-03-12 18:45:55Z

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

Collectives™ on Stack Overflow

Other method than Select in VBA [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related