CODEWITHSUNDEEP
excelvba
eM_Sk
eM_Sk

Reputation: 59

Find a range address #2

So because I wasn't specific with my previous question I need your help again.

Basically, I asked if there's a way to find a range between some value/cells, because I was sure that when i will get that range this below will work (so I can select lets say "headers" of the columns with all the data below:

totalRange(Selection, Selection.End(xlDown)).Select

So one of you came up with help and provide code below, which is working just fine, but i'm not sure if I can use it in my case. Because as I said, what im trying to do is to first find a range between two cells in firstrow and then select all the data below with it. Something like on screenshot below. I want to find Col7 and Col12 and then select the whole range below. The problem is this Col7/Col12 range might start from different column in each file.

https://ibb.co/gtuvEb 

Sub RangeBetween()


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

r1 = 0
r2 = 0

c1 = 1
c2 = 1
With Worksheets("Sheet1") 'Change to your worksheet

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

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

    On Error Resume Next
        r1 = Application.WorksheetFunction.Match("A", .Columns(c1), 0)
        r2 = Application.WorksheetFunction.Match("B", .Columns(c2), 0)
    On Error GoTo 0

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

End Sub

Thanks for any suggestions.

Upvotes: 1

Views: 73

Answers (2)

Jorge Acrisio
Jorge Acrisio

Reputation: 3

It seems that you try to look up certain values in the headers and select the ones between those columns. If I understood correctly, your question this can help you.

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("your sheet")

Dim header1 As Range, header2 As Range

On Error Resume Next
Set header1 = ws.Rows(1).Find(what:="your header value 1", LookIn:=xlValues, 
lookat:=xlWhole)
Set header2 = ws.Rows(1).Find(what:="your header value 2", LookIn:=xlValues, 
lookat:=xlWhole)
On Error GoTo 0

If Not header1 Is Nothing And Not header2 Is Nothing Then
    Range(header1, 
    header2).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Else:
    MsgBox "Header not fount"
End If

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

Sub RangeBetween()


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

With Worksheets("Sheet1") 'Change to your worksheet

    On Error Resume Next
        'Find the Columns
        c1 = Application.WorksheetFunction.Match("Col7", .Rows(1), 0)
        c2 = Application.WorksheetFunction.Match("Col12", .Rows(1), 0)
    On Error GoTo 0

    If c1 > 0 And c2 > 0 Then
        'Find last row with data
        r1 = .Cells(.Rows.Count, c2).End(xlUp).Row
        'Set the range to the whole
        Set totalRange = .Range(.Cells(1, c1), .Cells(r1, c2))
        totalRange.Select
    Else
        MsgBox "One or both items not found in range"
    End If
End With

End Sub

Upvotes: 1

Related Questions