Col number not letter


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,
 
Ad

Advertisements

P

Patrick Molloy

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
 
J

JLGWhiz

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.
 
Ad

Advertisements

G

Gary''s Student

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top