Name a Dynamic Range in VBA with varying Rows and Columns

[dversloot1]

Hello,

I'm trying to create a range in vba where the row is equal to a match found in column B and the columns are CV, DF, DP, and DZ.

I've managed to get the Row number by doing the following:

Row = Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A4"), Sheets("Data").Range("B1:B2000"), 0)

I'd like to get a Range based on the row number and the columns

Range = Sheets("Data").Range("CV" & Row),Sheets("Data").Range("DF" & Row), Sheets("Data").Range("DP" & Row), Sheets("Data").Range("DZ" & Row)

This was my poor attempt to get the range. Any suggestions for this?

 

[sijpie]

Two ways of doing this. I am assuming you are goint to use this range more than once in your code, in which case we don't want to calculate it every time, if it is only once then the first line gives the result.

Sub test()
    Dim rDyn As Range, lRow As Long
    
    lRow = Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A4"), _
            Sheets("Data").Range("B1:B2000"), 0)
    Set rDyn = Sheets("Data").Range("CV" & lRow & ",DF" & lRow & ",DP" & lRow & ",DZ" & lRow)
    
    'now you can use rDyn to do everything with:
    rDyn.Value = "Yipee it works!"
    rDyn.Font.Bold = True
    MsgBox rDyn.Address
    
    
    'clean up when finished
    Set rDyn = Nothing
End Sub