VBA: How to Return the Last Row and Column in Excel

VBA Header2

I was done with this week by about noon on Monday, so I’m four and a half days overdue for a Jessica Jones marathon.

Which is why I’m keeping this short and sweet. This week, I learned how to return the last row and column in an Excel spreadsheet. It’s insanely helpful if you want to copy and paste the same information to multiple workbooks using VBA.

Let’s start off by assuming you’re in a different spreadsheet and wish to first open up the workbook where things will be pasted. First, we want to grab the specific worksheet within that workbook like so:

Dim wbVariable As Workbook
Set wbVariable = Workbooks.Open(“C:\Filepath“)
Dim spVariable As Worksheet
Set spVariable = ActiveWorkbook.Worksheets(“WorksheetName“)

Exciting.

Now, let’s create two variables and make them equal to the first empty column and row.

lastcol = spVariable.Range(“A65536”).End(xlUp).Offset(1, 0).Column
lastrow = spVariable.Range(“A65536”).End(xlUp).Offset(1, 0).Row

This IT. This is so easy, it only took me 3 hours and 2 cups of coffee to figure out. Actually, it took about 5 minutes of Googling to find the End(xlUp) trick. 2 hours and 55 minutes to realize I needed to offset it by one to get the next EMPTY row.

Once you’ve got that you can set the value of that specific cell by calling on those two variables.

spVariable.Cells(lastrow, lastcol).Value = “Whatever you want

Alright, I’m off to watch Krysten Ritter beat up people for 8 hours straight. Adios.

 

 

 

Advertisements
Tagged , , , , , ,

4 thoughts on “VBA: How to Return the Last Row and Column in Excel

  1. curiousjatin says:

    There is an easier way

    lastcol = spVariable.UsedRange.Columns.Count
    lastrow = spVariable.UsedRange.Rows.Count

    This will work when you have more than 65536 rows

    Just for help, there are some essential standard functions that I have posted on my blog.

    https://curiousjatin.wordpress.com/2017/02/27/column-manupulation-vba-excel/

    Hope this helps
    CuriosJatin

    Liked by 1 person

  2. Alex Powers says:

    I would add that using CELLS as well to your arsenal CELLS(ROW, COLUMN) as you can see below take the first row and move all the way from the right to left and find the last column as well as take the total row count – either 65,536 from 2003 Excel or 1,048,576 from 2007 onward and then move up the first column. Obviously can be adapted as well if your data doesn’t start in the first column in the top most cell. But new tricks I thought I’d pass along! Great site by the way.

    lastcol = spVariable.Cells(1, Columns.Count).End(xlLeft).Column
    lastrow = spVariable.Cells(Rows.Count, 1).End(xlUp).Row

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: