CODEWITHSUNDEEP
excelvba
eM_Sk
eM_Sk

Reputation: 59

Find a range address

I want to select some range based on cells value, as they might be each time in different column.

So my first thought was something like below, but I'm not sure if that's the right way? 

Sub RangeBetween()

Dim rng1 As Range, rng2 As Range
Dim totalRange As Range
Dim c1, c2 As Integer

c1 = 1
    Do Until Name = "A"
        Name = Cells(1, c1)
        c1 = c1 + 1
    Loop
someA= c1 - 1

c2 = 1
    Do Until Name = "B"
        Name = Cells(1, c2)
        c2 = c2 + 1
    Loop
someB= c2 - 1

Set rng1 = Range("???")
Set rng2 = Range("???")

Set totalRange = Range(rng1.Address & ":" & rng2.Address)
    totalRange .Select
    End Sub

Thanks

Upvotes: 1

Views: 74

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Or you can use Match()

Sub RangeBetween()


Dim totalRange As Range
Dim c1 As Long, c2 As Long


c1 = 0
c2 = 0
With Worksheets("Sheet1") 'Change to your worksheet
    On Error Resume Next
        c1 = Application.WorksheetFunction.Match("A", .Rows(1), 0)
        c2 = Application.WorksheetFunction.Match("B", .Rows(1), 0)
    On Error GoTo 0

    If c1 > 0 And c2 > 0 Then
        Set totalRange = .Range(.Cells(1, c1), .Cells(1, c2))
        totalRange.Select
    Else
        MsgBox "One or both items not found in range"
    End If
End With

End Sub

Upvotes: 3

Related Questions