VBA:找到列标题后设置列范围

Question

我希望能够为要搜索的列标题下的第二个单元格设置到列底部的范围。我不想选择整个列，而只是从第二个单元格开始使用的范围(不包括标题)。

我能够编写一些代码来查找标题，但是在将单元格地址(字符串)转换为范围然后为该列的其余部分选择使用的范围时遇到了一些问题。这是我到目前为止的情况:

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

谢谢您的帮助!

Answer 1

正确的做法是

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

示例 1



示例 2



Interesting Read on how to find the last row correctly