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.
gifGuide2Code 
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
LikeLiked by 1 person
NICE. That’s really good to know. Though I think if ever I make a spreadsheet more than 65536 rows, I’m giving up my job, robbing a bank, and moving to Mexico.
LikeLiked by 1 person
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
LikeLiked by 1 person
Ohhhhhh. That’s a NICE one. Thanks, I’ll remember that 🙂
LikeLike