asked on

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

Saurabh Singh Teotia

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

excelismagic

Saurabh

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

:)

Saurabh Singh Teotia

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

excelismagic

:-)

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 Teotia

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

excelismagic

:-)

nice to know you Saurabh

thanks.

Saurabh Singh Teotia

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

bobrossi56

ASKER

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

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

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

bobrossi56

ASKER

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 Teotia

Glad to know..Then you are all set.. Happy to help.. :-)

You are welcome..

Saurabh...

bobrossi56

ASKER

Works great...thx