Use of column labels in VBA

[neilby]

Dear all, as you will see below I remain not even a novice at VBA despite the length of membership.

I have some VBA written by a third party where cell refs are used rather than range names.

I appreciate at some point I need to probably try to spend some years completely re-writing to work in a more elegant manner but for now....

I have a requirement for VBA that is to work on numerous worksheets that can contain different no of rows and different no of columns

existing code aimed at a reliably sized (as far as columns are concerned) worksheet looks like this:

lastrow = Range("A1000000").End(xlUp).row
Range("L4:AQ4").Select
Selection.AutoFill Destination:=Range("L4" & ":AQ" & lastrow)


if possible, to stay in this sort of coding format, is there a way to identify the last column in the same manner as the lastrow has been identified above BUT then also be able to save the column label of that last column (eg "AQ") in an array for later use rather than the column number to provide something like:

for use in a formulae like
Selection.Autofill Destination:=Range("L4" & ":" & lastcol & lastrow).Select

If not, any suggestions for an alternative

Thanks and regards for your condolences/scorn and help in advance.

 

[Norie]

You could just use the column number.

lastrow = Range("A" & Rows.Count).End(xlUp).Row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column

Selection.Autofill Destination:=Range("L4", Cells(lastrow, lastcol))

 

[neilby]

Thanks Norie, obviously worked first time.

Most kind