VBA : Excel Range Selection Problem

[Learner]

I am experiencing a unique situation about the Excel Range. This is the
code :


Range(Cells(7, 6), Cells(7, intLastColumn).End(xlDown)).Select
Selection.NumberFormat = "0"

Its working most of the time and changing the format to "0" as desired.
(Columns will contain numerics only) But some times, For some wiered
reason, it wont reach till the last row. Anyone has similar experience.

Appreciate help.

 

[Guest]

I prefer using the UsedRange property to determine the number of rows and
columns being used. Try replacing your first line of code with the following.

Range(cells(7,6),cells(7,usedrange.columns.count)).select

http://HelpExcel.com

 

[Learner]

Thanks for the reply!
Unfotunately, I got a Variable Not defined 'UsedRange' Error.
Am i mising something ?

 

[Guest]

My fault. You probably have to include the Sheet object, e.g.,

SheetX.UsedRange.Columns.Count

http://HelpExcel.com

 

[Learner]

Thanks for the prompt repsonse.
I got it working with Activesheet.UsedRange
But it is selecting only ALL the columns...I am looking for something
that would select all the ROWS

 

[Tom Ogilvy]

if the last column has an empty cell, then you would have this problem.
does your last column have an empty cell within the last column in the data
you want to select?


Perhaps

Dim rng as Range
set rng = cells(rows.count,intLastColumn).End(xlup)
Range(Cells(7, 6), rng).Select
Selection.NumberFormat = "0"

or if Column 1 would be the best column to find the last filled row

Dim rng as Range
set rng = cells(rows.count,1).End(xlup)
Range(Cells(7, 6), Cells(rng.row, intLastColumn)).Select
Selection.NumberFormat = "0"

UsedRange is not always reliable for what you want to do, especially if you
will be deleting data at the bottom of your populated area.

 

[Learner]

Exactly. it was the problem. I figured it just before I read this post.
Anyways, Thanks a million.