R1C1 style variables

[Andybeary]

Hi

I need to have a macro to format a sheet which is always different

im using

Dim b as integer

b = selection.currentregion.columns.count

(because b is now a number i need to use this in the range select.)

I want it to select A4:E4 however due to the b being a number it cant unless i change it to R1C1 style, something like (i know this is wrong) range("R4C1:R4C5") or as my variable ("R4C1:R4C" & b)

can anyone help?

thanks

 

[Joe4]

Try using CELLS with RANGE, i.e.

To select the range A4:E4, you can use:
RANGE(CELLS(4,1),CELLS(4,5))
or if b is the row number:
RANGE(CELLS(b,1),CELLS(b,5))

 

[just_jon]

Can also use:

...RANGE("A" & b & ":B" & b)

to define the range A4:B4 given b == 4

 

[Andybeary]

Both work a treat thanks guys