I have a VBA macro that does a lot of things to different files and it works fine, but I want to add more to the macro where it will select cells from A1 to the last cell in the last column so I can format those cells a certain way, and of course each file has a different ending row and column. I was using this but I had to manually change the range in the macro for each file before running it.
Selection.NumberFormat = '#,##0'
I suspect there must be a way to determine the last row/column and insert that variable into the range select statement?
thx experts...BobR![Vba Vba](/uploads/1/2/4/7/124750111/477931887.jpg)
![Last Last](/uploads/1/2/4/7/124750111/168829050.png)
Shortest Code for finding last row via VBA I'm wondering why the following doesn't seem to be encouraged on ANY Excel site I've ever seen. I've seen lots of code for determining the last row but this is the shortest. Let me know in the comments section below how you use resizing a range in your macro code! Also, if you can think of any other ways to use VBA code to find the last row or last column, post your coding method in the comments section so we can improve the current list.
Range('A1:Q139').SelectSelection.NumberFormat = '#,##0'
I suspect there must be a way to determine the last row/column and insert that variable into the range select statement?
thx experts...BobR
I am trying to make this way of finding the last row as I found the last column:
![Vba Vba](/uploads/1/2/4/7/124750111/477931887.jpg)
I know how to do it this way but it is not as helpful as the prior would be:
I tried doing this:
Any help would be greatly appreciated.
Synopsis:Would like the below way again for last row. Thanks
Alexis MorenoAlexis Moreno
3 Answers
You should use a with statement to qualify both your
Rows
and Columns
counts. This will prevent any errors while working with older pre 2007 and newer 2007 Excel Workbooks.Last Column
Last Row
![Last Last](/uploads/1/2/4/7/124750111/168829050.png)
Or
user6432984
This is the best way I've seen to find the last cell.
One of the disadvantages to using this is that it's not always accurate. If you use it then delete the last few rows and use it again, it does not always update. Saving your workbook before using this seems to force it to update though.
Using the next bit of code after updating the table (or refreshing the query that feeds the table) forces everything to update before finding the last row. But, it's been reported that it makes excel crash. Either way, calling this before trying to find the last row will ensure the table has finished updating first.
Another way to get the last row for any given column, if you don't mind the overhead.
Matthew GoheenMatthew Goheen
Juan CappuccinoJuan Cappuccino