代码之家 › 专栏 › 技术社区 › Eitel Dagnin

VBA-清除最后一行的已找到(动态)范围的单元格

vba excel

Eitel Dagnin · 技术社区 · 7 年前

这和这里的问题差不多:

Excel VBA - How to clear a dynamic range (from below header, to last row)

然而,考虑到这是一个旧的线程和提供的解决方案不起作用,我张贴的问题在我试图解决我的问题的方式。

这里找到了两种查找正确列的解决方案:

VBA - Find a column with a specific header and find sum of all the rows in that column

https://www.extendoffice.com/documents/excel/4879-excel-select-column-by-header-name.html

清除此处内容的解决方案:

Excel VBA-如何清除动态范围(从页眉下方到最后一行)

以下是我努力工作的地方:

With ActiveSheet

    xStr = "Product Folder"

    Set aCell = .Range("B2:J2").Find(What:=xStr, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

    'If Found
    If Not aCell Is Nothing Then

        col = aCell.Column
        colName = Split(.Cells(, col).Address, "$")(1)

        lRow = .Range(colName & .Rows.count).End(xlUp).Row

        'This is your range
        Set Rng = .Range(colName & "2" & lRow)

    'If not found
    Else

        MsgBox "Column Not Found"

    End If

    Range(Rng).Resize(Rows.count - 1, Columns.count).Offset(1, 0).ClearContents

End With

我可以得到所需的所有信息(列、标题、范围等),但似乎找不到清除内容的方法。

使用上面的代码,我得到错误:

对象'\u Global'的方法'Range'失败

我试过改变

    lRow = .Range(colName & .Rows.count).End(xlUp).Row

    'This is your range
    Set Rng = .Range(colName & "2" & lRow)

对此:

    lRow = Range(colName & .Rows.count).End(xlUp).Row

    'This is your range
    Set Rng = Range(colName & "2" & lRow)

这一行:

Range(Rng).Resize(Rows.count - 1, Columns.count).Offset(1, 0).ClearContents

对此:

Rng.Resize(Rows.count - 1, Columns.count).Offset(1, 0).ClearContents

但后来我发现这个错误:

应用程序定义或对象定义错误

尝试更改此:

    'If Found
If Not aCell Is Nothing Then

    col = aCell.Column
    colName = Split(.Cells(, col).Address, "$")(1)

    lRow = .Range(colName & .Rows.count).End(xlUp).Row

    'This is your range
    Set Rng = .Range(colName & "2" & lRow)

'If not found
Else

    MsgBox "Column Not Found"

End If

Range(Rng).Resize(Rows.count - 1, Columns.count).Offset(1, 0).ClearContents

对此:

'If Found
    If Not aCell Is Nothing Then

        col = aCell.Column
        colName = Split(.Cells(, col).Address, "$")(1)

        lRow = Range(colName & .Rows.count).End(xlUp).Row + 1

        Set myCol = Range(colName & "2")

        'This is your range
        Set Rng = Range(myCol & ":" & colName & lRow)

    'If not found
    Else

        MsgBox "Column Not Found"

    End If

    Rng.Clear

然后我在 Set Rng

对象'\u Global'的方法'Range'失败

我知道这行代码本身并不正确(很明显),但我不知道如何让代码读成 Range("A2:A" & Lastrow).Clear

1 回复 | 直到 7 年前

Vityata 7 年前

这确实是导致错误的原因:

Set Rng = Range(myCol & ":" & colName & lRow)

这个 Range() 需要范围或字符串参数。

不是两者都有。试试这个: Set Rng = Range(myCol.Address & ":" & colName & lRow)

Sub TestMe()

    Dim rng As Range
    Dim myCol As Range
    Dim colName As String
    Dim lRow As Long

    Set myCol = Range("A5")
    colName = "A"
    lRow = 15
    Set rng = Range(myCol.Address & ":" & colName & lRow)    
End Sub

或者通过传递两个范围对象来尝试此操作:

With Worksheets(1)
    Set rng = .Range(myCol, .Cells(lRow, colName))
End With