How to set "A1:A" & to variable and convert column number to letter

[desantech]

Hello
I want to ask you ist there a way to show this line lets say:

Range("O1:O" & .Cells(.Rows.Count, "O").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
In variables?

(My worksheet has some rows and in last column "O" there are info, header go only to Column "N")

I have tried this, and it works, but looks very like putting puzzles together:

Dim LastColumn As Long
Set sht = ThisWorkbook.Worksheets("temp3")
LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column - 1 'I had to put in "-1" else it would put me to Column "P" not "O"
With Sheets("Temp3")

DimlastCol$
'lastCol = Split(Columns(Range("A1").End(xlToRight).Column).Address(, False), ":")(1) 'this also works but gets me to column "N" not "O" – because 1. Row is only till N, so I dont use it and use this better:

lastCol = Split(Sheets("temp3").Cells(, LastColumn).Address, "$")(1) ' here I get the lastcolumnr as Letter

'Finally what I had to put together to have it work:
With .Range(lastCol & "1" & ":" & lastCol & .Cells(.Rows.Count, lastCol).End(xlUp).Row).SpecialCells(xlCellTypeConstants)

Its not important to answer, because it works, but any better solution (faster) would be nice to know

Thank you


variable.jpg

[desantech]

PS I have posted it on excel-exchange

[mana]

Set sht = ThisWorkbook.Worksheets("temp3")
LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column - 1 
With sht.Range(sht.Cells(1, LastColumn), sht.Cells(sht.Rows.Count, LastColumn)).SpecialCells(xlCellTypeConstants)

or

Set sht = ThisWorkbook.Worksheets("temp3")
LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column - 1 
With sht.Columns(LastColumn).SpecialCells(xlCellTypeConstants)