CODEWITHSUNDEEP
vbaexcel
wra
wra

Reputation: 257

VBA: Setting column range after finding column header

I want to be able to set a range for the second cell under the column header I am searching for to the bottom of the column. I don't want to select the entire column, but just the used range starting from the second cell (not including the header).

I was able to write some code to find the header, but I'm having some issues converting the cell address (string) to a range and then selecting the used range for the rest of that column. Here is what I am so far:

Sub colRange()

Dim ws As Worksheet
Dim hostCellRange As Range

Set ws = Worksheets("Sheet1")

With ws
    With .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft))
        Set cfind = .Find(What:="host", LookIn:=xlValues, lookat:=xlWhole)
        If Not cfind Is Nothing Then
            hostCell = cfind.Address
            Set hostCellRange = ws.Range(hostCell)
        End If
    End With
End With


End Sub

Thank you for your help!

Upvotes: 1

Views: 1855

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

The correct way to do it will be

Option Explicit

Sub colRange()
    Dim ws As Worksheet
    Dim hostCellRange As Range, cfind As Range
    Dim lRow As Long, lCol As Long

    Set ws = Worksheets("Sheet1")

    With ws
        With .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft))
            Set cfind = .Find(What:="host", LookIn:=xlValues, lookat:=xlWhole)
            If Not cfind Is Nothing Then
                '~~> Find the column number
                lCol = cfind.Column
                '~~> Find the last row in that column
                lRow = ws.Range(Split(Cells(, lCol).Address, "$")(1) & ws.Rows.Count).End(xlUp).Row
                '~~> Create the range
                Set hostCellRange = .Range(cfind.Offset(1, 0), cfind.Offset(lRow - 1, 0))
                Debug.Print hostCellRange.Address
            End If
        End With
    End With
End Sub

Example 1

enter image description here

Example 2

enter image description here

Interesting Read on how to find the last row correctly

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33692

Try setting hostCellRange using the Offset from cfind, you don't need the Address for that.

If Not cfind Is Nothing Then
    Set hostCellRange = .Range(cfind.Offset(1, 0), cfind.End(xlDown))
End If

Note: cfind.End(xlDown) will work with range without empty cells in the middle.

Upvotes: 1

Related Questions