VBA, how to obtain last column as range in a given range?

user_8853431 0
Viewed 6940

I have a very simple question, which unfortunately I can not resolve. Thus would appreciate your help. Here is the thing: I should obtain last column from the range as a range. For example, if I have A26:D32, I should get D26:D32 as a result and input into the loop.

This is the code I have so far: 

Function getSmth(CustomCol As Range)
    Dim i As Double
        For Each cell In CustomCol  'start of the loop. CustomCol here should return D26:D32 already
            If cell.Value > i Then
                i = cell.Value
            End If
        Next
....

End Function

What I have tried to do was writing CustomCol.Columns(6), as I know the last column, but it did not work out.

Would really be glad for your help!

vba excel
4 Answers

You can try this:

Function GetLasRangeCol(rng As Range) As Range
    Set GetLasRangeCol = rng.Columns(rng.Columns.Count).Cells
End Function

That you may use in your calling code as:

For Each cell In GetLasRangeCol(myRange)

Where ‘myRange’ is a valid range reference, i.e: either a variable of Range type or some Range object (like ‘Range(“A5:B21”)’) to get last column out of

user_93128920

There's probably a shorter way to it, but this does the trick:

Function lastColumn(rg As Range) As Range
    Set lastColumn = Range(Cells(Range(Split(rg.Address(0, 0), ":")(0)).Row, _
        Range(Split(rg.Address(0, 0), ":")(1)).Column).Address(0, 0) _
        & ":" & Split(rg.Address(0, 0), ":")(1))
End Function

It take a Range object as a parameter and returns a Range object.

Example Usage:

Sub demo()
    Dim rg As Range, rg2 As Range
    Set rg = Range("A26:D32")
    Set rg2 = lastColumn(rg)
    Debug.Print "The last column is: " & rg2.Address
End Sub

...returns: The last column is: $D$26:$D$32

There's probably a "tidier" method (perhaps using INTERSECT) but this will work fine.

Explanation:

This is the same function as above, but broken down so it's easier to understand:

Function lastColumn(rg As Range) As Range

    Dim firstRow, lastRow, firstCol, lastCol, leftPart, rightPart

    leftPart = Split(rg.Address(0, 0), ":")(0)
    rightPart = Split(rg.Address(0, 0), ":")(1)
    firstRow = Range(leftPart).Row
    firstCol = Range(leftPart).Column
    lastCol = Range(rightPart).Column

    Set lastColumn = Range(Cells(firstRow, lastCol), Range(rightPart))

End Function

More Information:

user_81127760

If you want the last column as a range, then myRange.Columns(myRange.Columns.Count)

If you want the number of the last column then myRange.Columns(myRange.Columns.Count).Column

user_33669810

To find last column in row 1:

Dim LastCol As Long

LastCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
user_89810660
Related