VBA - returning a column letter

[Khazimula]

I've got a conundrum that's I've been mulling over all night with no luck. I am hoping someone on here knows a solution.

I am trying to reference a set range of rows within the active column (which will of course differ depending on which cell is selected when the macro is run). I know I can return the column number using activecell.column, and I was thinking I could perhaps change this into a letter (prfereably not using a table in Excel) and then have this as part of a range. Something along the lines of:

Range(ActiveCell.Column & "2" & ":" & ActiveCell.Column & "11").Select

...but with activecell.column returned as a letter.

I'm not certain this is the best way of going about it. I'm also aware there is something called R1C1, but its not an area of VBA I am familiar with and the help file on it hasn't been that helpful.

Any ideas on this would be greatly appreciated. I have a feeling the solution is right under my nose, but at the moment my nose is too big to allow me to see it.

 

[Richard Schollar]

If you want to select the column of the activecell then you could use:

Columns(ActiveCell.Column).Select

Regards

Richard

 

[Fat Cat]

A couple of ways of making up the range;

Dim myRange As Range

Set myRange = Range(Cells(ActiveCell.Row + 2, ActiveCell.Column), Cells(ActiveCell.Row + 11, ActiveCell.Column))
MsgBox (myRange.Address)

Or,

Dim myRange As Range

Set myRange = Range(ActiveCell.Offset(2, 0).Address, ActiveCell.Offset(11, 0).Address)
MsgBox (myRange.Address)

 

[Khazimula]

Thanks for the response Parsnip. Its got me thinking, but its not exactly what I'm trying to do. I don't want to select the entire column, just the part of the column that lies between two fixed row numbers (rows 2:11). If I could select the column and then within the column select the range between these two rows then that would work...

 

[Khazimula]

Brilliant Fat Cat. Both methods should work with a bit of tweaking. I particularly like your first solution!

Thanks very much to you all

 

[al_b_cnu]

Hi,

This UDF returns the column letter of the column supplied:

Function GetCol(ByVal ColRqd As Integer) As String
Dim sCol As String
sCol = Cells(1, ColRqd).Address(True, False)
GetCol = Left$(sCol, InStr(sCol, "$") - 1)
End Function

 

[Fat Cat]

If the cells you want are a range of 10 cells always Offset by 2 from the active cell just use the Offset function

If your ActiveCell.Address is $H$19, then

ActiveCell.Offset(2,0).Address = $H$21
ActiveCell.Offset(11,0).Address = $H$30

Range(ActiveCell.Offset(2,0).Address , ActiveCell.Offset(11,0).Address).Address = $H$21:$H$30