VBA: Setting column range after finding column header

Question

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!

will you have blank cells in your range? – Siddharth Rout Nov 24 '17 at 16:00 — Siddharth Rout, Nov 24 '17 at 16:00

Siddharth Rout · Accepted Answer · 2017-11-24T16:14:01.413

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

Example 2

Interesting Read on how to find the last row correctly

Hey Siddharth, thank you for taking the time to explain the code as well! I will be using this! I appreciate the help! — wra, Nov 24 '17 at 16:14

Shai Rado · Answer 2 · 2017-11-24T16:02:28.617

1

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.

edited Nov 24 '17 at 16:02

answered Nov 24 '17 at 15:59

Shai Rado

33,032
6
29
51

VBA: Setting column range after finding column header

2 Answers2