CODEWITHSUNDEEP
excelvbaworksheet-functionworksheettext-extraction
H Foucault
H Foucault

Reputation: 57

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 ?

Upvotes: 2

Views: 19877

Answers (5)

QHarr
QHarr

Reputation: 84465

Or 

Option Explicit
Public Sub test()
    Const columnNumber As Long = 4
    Dim rngaddy As Range
    Set rngaddy = Intersect(Columns(2), ActiveSheet.UsedRange): Debug.Print rngaddy.Address
End Sub

Upvotes: 1

DisplayName
DisplayName

Reputation: 13386

to get the real UsedRange of a columns you could use:

With Columns(columnNumber).SpecialCells(xlCellTypeConstants)
    Set rngaddy = .Parent.Range(.Areas(1), .Areas(.Areas.Count))
End With

where rngaddy is a Range object

of course what above would fail if the column has no "constant" cells, then you may want to add some error trapping or entry check (e.g. If WorksheetFunction.CountA(Columns(columnNumber)) = 0 Then Exit Sub

Upvotes: 1

jeffreyweir
jeffreyweir

Reputation: 4824

My preferred method is to use ListObjects aka Excel Tables to hold any input data whenever I possibly can. ListObjects are named ranges that Excel automatically maintains on your behalf, and because they grow automatically when new data is added, they give you a very robust way of referencing ranges in Excel from VBA, that is more immune to users doing things that might otherwise break code reliant on the .End(xlUp) approach.

enter image description here

? Range("MyTable").ListObject.ListColumns("Column 1").DataBodyRange.Address
$A$3:$A$7

Often I'll give the column concerned a named range of its own, in case the user (or a developer) later wants to change the Table column name, and use that name in my code instead.

enter image description here

? Range("FirstColumn").Address
$A$3:$A$7

If somebody (perhaps me) adds rows/columns above/left of the range of interest or shuffles the order of Table columns around, or changes the name of a column, the code still references the intended range and doesn't need to be changed.

enter image description here

? Range("FirstColumn").Address
$C$4:$C$8
? Range(Range("FirstColumn").Address & ":" &  Range("FirstColumn").EntireColumn.cells(1).address).Address
$C$1:$C$8

Granted, that method of getting the range from the top cell (which may be above the ListObject) to the bottom of the column concerned is kinda long, but once you start using ListObjects more in your code you normally don't care what is above or below them...you just want the goods held inside.

I haven't used .End(xlUp) in years, other than to find where my data ends should I be in the process of turning it into a ListObject. But I'm a ListObject evangelist...your mileage may vary :-)

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21639

You could return the last populated cell is in columns # col with this:

MsgBox Cells(sht.Rows.Count,col).End(xlUp).Address

If you want to return the first populated cell as well, you could use:

MsgBox IIf(IsEmpty(Cells(1,col)),Cells(1,col).End(xlDown),Cells(1,col)).Address

Therefore this would return only the "used" range of Column #4 (D):

Sub Example_GetUsedRangeOfColumn()
   Const col = 4
   Dim sht As Worksheet
   Set sht = Sheets("Sheet1")
   MsgBox Range(IIf(IsEmpty(Cells(1, col)), Cells(1, col).End(xlDown), _
       Cells(1, col)), Cells(sht.Rows.Count, col).End(xlUp)).Address
End Sub

  • So with this example:

    img ...the above procedure would return: .

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

   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)

Upvotes: 3

Related Questions