Setting Range? (Excel 2003)

Topic

New Reply

Don’t you mean “… for a column that is variable)? You can use

Range(Cells(1, 3), Cells(1, ColCnt))

or

Range(“C1”).Resize(1, ColCnt – 2)

(and the method to select a range is .Select, not .Selection)

Reply | Quote

Replies

Thanks Hans,

Exactly what I need.

(Will edit my question so it doesn’t confuse someone else.)

Reply | Quote

Can anyone tell me the format for setting a Range for a Columns that are variable.

I have determined the numeric position of the last column and stored it in a variable called ColCnt. If it is 7 then I want Cell ‘G1’ if it is 27 then ‘AA1’

What I need is if ColCnt = 7 then

Range(“C1:G1”).select

Thank you

Reply | Quote

See Column Numbers To Letters for a function that convets the number to the corresponding column letters.

Reply | Quote

Thank you Tony.

This has now become part of my Excel Library.

Reply | Quote

Hi Hans:

I find that this cells syntax within the Range method fails when I automate Excel from Access via VBA using Office 2003 on Windows XP.

For example:

Worksheets(“Sheet1”).Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True

and

Set rng = osheet.Range(Cells(1, 1), Cells(1, intCols))

both fail. Any idea why or how to fix it?

Thanks!

Reply | Quote

Just guessing but have you tried..

Worksheets(“Sheet1”).Range(Range(Cells(1, 1), Cells(5, 3))).Font.Italic = True

..I seem to remember someting about having to use Range twice occsionally

zeddy

Reply | Quote

No, but I figured it out:

You have to activate the sheet object first. Then, it works fine.

(Why, you ask????)

Reply | Quote

By default, Cells, like Range, refers to the active sheet, therefore they all need to be prefixed with the sheet object (or you can activate the sheet, but it’s better practice to use the worksheet object).

Reply | Quote

As rory mentioned, i would not activate the sheet. i would use the sheet object explicitly:

Set rng = osheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, intCols))

Or use a with construct:
With osheet
Set rng = .Range(.Cells(1, 1), .Cells(1, intCols))
end with

Steve

Reply | Quote

I appreciate this hint on not activating the worksheet. It really is more efficient (and easier to understand).

Thanks

Reply | Quote

Kathryn,
You need to prefix the Cells calls with oSheet too:
Set rng = osheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, intCols))

HTH

Reply | Quote