activesheet in excel VBA

Question

Appologies for wery simple question i have, i'm just new in programming. Is the Activesheet in excel VBA means that if you have a code

Sheets("Point Assignments").Select
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & lastrow).Select
Application.CutCopyMode = False
Selection.Copy

The ActiveSheet will be the "Point Assignments"

Also, in the lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row what is meant by .END(xlup). Row

Thanks very much for the help

Regards, Kenneth

.End(xlUp) is the equivalent of hitting Ctrl + Up in a data range. It will take you to the end of a contiguous range of blank or filled cells. There is also xlDown, xlLeft and xlRight. So in the case of your code, it finds the last used row of data by starting at the very last cell and moving up to the first row of data from the bottom. Tim Williams addressed your other question in the answer. — Scott Holtzman

Tim Williams Tim Williams · Accepted Answer · 2015-11-21T01:31:24

Yes, but using ActiveSheet and not explicitly referring to a specific sheet does not make for good code.

It's difficult in a larger project to track which sheet is supposed to be active at any given time, particularly if you're calling out to other procedures which also interact with the workbook, and might leave the workbook in a different state from where it began.

So - a better approach would be something like:

With Sheets("Point Assignments")
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("B2:B" & lastrow).Copy
End With

or simpler:

With Sheets("Point Assignments")
    .Range(.Range("B2", .Cells(.Rows.Count, 1).End(xlUp)).Copy
End With

activesheet in excel VBA

1 Answers