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