CODEWITHSUNDEEP
vbaexcelrow
Mardhain
Mardhain

Reputation: 13

Get last row but offset result to another column

basically I set a range for my macro, but I want to be able to get last row as it will change everytime with different database.

Sub GetRegion()
Dim res As Variant
Dim RegionRange As Range, InitialRange As Range, c As Range
Set RegionRange = Range("K2:K4657")
Set InitialRange = Sheets("Matching Table").Range("A3:C114")
For Each c In RegionRange
    res = Application.VLookup(c, InitialRange, 3, False)
    If Not IsError(res) Then
        c.Offset(0, 5).Value = res
    End If
Next c
End Sub

It will dropdown all results let's say from c which is in column K, but offset into column P ( for every row)

Upvotes: 0

Views: 914

Answers (1)

BerticusMaximus
BerticusMaximus

Reputation: 725

Using & in your Range we can change the last row of your data. See below for two different ways to calculate the last row. This is a good resource if you more ways to find the last row of data.

'Option 1 find last row in col K (slightly faster)
With ActiveSheet
    LastRow = .Range("K" & .Rows.Count).End(xlUp).Row
    Set RegionRange = .Range("K2:K" & LastRow)
End With

'Option 2 find last row of entire worksheet (slower)
With ActiveSheet
    LastRow = .Cells.Find(What:="*", _
        After:=.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
    Set RegionRange = .Range("K2:K" & LastRow)
End With

Upvotes: 2

Related Questions