Col number not letter

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

I recorded a macro which gave

Columns("A:M").Select

However, I need my code to be a little adaptive so I initially determine the
last column in the row

Range("A1").Select
Selection.End(xlToRight).Select
lstColCell = ActiveCell.Address
lstCol = ActiveCell.Column

As such, lstCol returns a number, not a letter like the nicely generate
macro code requires. How can I use the lstCol in the macro code?

I tried
Columns("1:" & lstCol).Select

But this didn't work.

Thank you for the help

Qb,
 
you almost had it ...

With Range("7:" & lstCol
'do something
Next


The example is deliberate ...you don't need to select a range to use it.
to color the above rave, just add the method

.Interior.Color=vbRed
.Font.Bold = True
 
In the VBA help file there are several sections that provide detailed
descriptions of how to use various references of cells, rows and columns.
To access these files, press Alt +F11, then open the help facility and ente
"How to Reference Cells and Ranges" in the help search box and press enter.
When the topic menu appears click on that topic. It will display about ten
additional topics that give various eplanations and examples of how to use
the A1 notations and the index notations for referencing cells, rows and
columns.
 
You need to convert your column numbers into a string with letters. For
example, we want columns G thru S:

Sub columnAte()
n1 = 7
n2 = 19
Set r = Range(Cells(1, n1), Cells(1, n2))
s = r.Address(RowAbsolute:=False, ColumnAbsolute:=False)
s = Replace(s, "1", "")
MsgBox (s)
Columns(s).Select
End Sub
 
Back
Top