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
– Siddharth Rout

2017-11-24 16:00:15 +00:00
Commented Nov 24, 2017 at 16:00 — Siddharth Rout
– Siddharth Rout, Commented Nov 24, 2017 at 16:00

Siddharth Rout · Accepted Answer · 2017-11-24 16:14:01Z

2

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

edited Nov 24, 2017 at 16:14

answered Nov 24, 2017 at 16:08

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wra Over a year ago

Hey Siddharth, thank you for taking the time to explain the code as well! I will be using this! I appreciate the help!

Shai Rado · Accepted Answer · 2017-11-24 16:02:28Z

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, 2017 at 16:02

answered Nov 24, 2017 at 15:59

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Collectives™ on Stack Overflow

VBA: Setting column range after finding column header

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related