Reputation: 257
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
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
Example 2
Interesting Read on how to find the last row correctly
Upvotes: 2
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