select a range in a VBA macro from A1 to last row/column cell

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.

Range("A1:Q139").Select
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
bobrossi56Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I'm assuming this is what you are looking for...

Dim lrow As Long
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:Q" & lrow).NumberFormat = "#,##0"

Open in new window


This will find the last row basis of A column of your worksheet and will change the same to number format...

Saurabh...
excelismagicCommented:
Saurabh

you are too fast.  you dont let anyone make some score. do you?

:)
Saurabh Singh TeotiaCommented:
Excel Magician.. Thanks..And Nah i don't get all of them..even though if i wish for the same....They are lot of talented experts here who beats me lot of times.. :-)

I like to have a head start though... :-)

Saurabh...
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

excelismagicCommented:
:-)  

no body can beat fast.  

sometimes, i think you answer the question on the fly, before even the OP clicks the submit button :-)

i get auto notification from system and when i open the email, i see already "Solved by Saurabh"

your name sounds persian, is it?
Saurabh Singh TeotiaCommented:
Excel Magician..

I wish i could have solved it as soon the author..Think About it..but unfortunately technology doesn't allows me to... ;-)

And I don't check emails from EE anymore for questions since i'm here on forums most of the time..and i like their system of alert since you can see the same on EE itself..so you see you can be faster.. :-)

And I'm Indian.. :-)
excelismagicCommented:
:-)

nice to know you Saurabh

thanks.
Saurabh Singh TeotiaCommented:
Same here..and Let's stay off this post before both me and you get reprimanded by a Mod or Community  adivisor for our personal talks.. :-)
bobrossi56Author Commented:
Saurabh, I tried your solution and its almost working, what I mean is it formats the cells as numbers, but not all the way down, for example I have a file with 138 rows in it, I run your code and it formats all the cells as numbers to row 119, the rest of the rows to 138 are not formatted correctly. I try a different file with 248 rows, it formats 205 rows as numbers and the rest of the rows to 248 are not touched, its almost like the code is not actually finding the LAST row...
Saurabh Singh TeotiaCommented:
bobrossi56,

It can happen in two situations:-

1. I'm determining the last row basis of A Column..However A Column doesn't have the last filled row and i'm assuming you don't know what column will have the last filled row then use this line..rather then earlier one which is...

lrow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Open in new window


And then rest of the code which is...

Range("A1:Q" & lrow).NumberFormat = "#,##0"

Open in new window


2. Your worksheet which is active and then one in which you are doing are two different worksheets which i don't think that's the case here...

Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Is the data continuous, ie no wholly blank rows or columns?

If so, you can use:

Range("A1").Select
CurrentRegion.Select

The manual keyboard equivalent to test before using in VBA is:

Ctrl + *

The * can be above the number pad or as combination of Shift + 8

Thanks
Rob H
bobrossi56Author Commented:
Sorry....my error, the range was not from A1 but for E1, I changed the last line but did not change the second line. DUH!!!

Dim lrow As Long
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("E1:Q" & lrow).NumberFormat = "#,##0"

This works great now...

Dim lrow As Long
lrow = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Range("E1:Q" & lrow).NumberFormat = "#,##0"

Thanks you so much
Saurabh Singh TeotiaCommented:
Glad to know..Then you are all set.. Happy to help.. :-)

You are welcome..

Saurabh...
bobrossi56Author Commented:
Works great...thx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.