Search code examples
excelvbaworksheet-functionworksheettext-extraction

vba column address from column number

I have a column number , say columnNumber = 4 . I need the used range of this column. I know how to find the last used row, and I could convert the column number to a column number like so

ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)
LastRow = sht.Cells(sht.Rows.Count, ColumnLetter).End(xlUp).Row

and then build an address like so

rngaddy = ColumnLetter & "1:" & ColumnLetter & LastRow

and finally do 

Range(rngaddy)

But is there an easier way to find the complete used range of a column given it's number ?

Solution 
  •    Dim rngaddy As Range
    With Sheet1
        Set rngaddy = .Range(.Cells(1, 4), .Cells(.Rows.Count, 4).End(xlUp))
    End With

    and if, for some reason, you want to see the address in A1 notation, merely:

    debug.print rngaddy.address

    Note that in doing it this way, rngaddy is, itself, the range object and not a string. So no need to do Range(rngaddy)