excel - 根据标题从列中删除单元格范围

Question

请注意，我是 VBA 新手

我有很多专栏。如果任何列的标题为“X”，我希望它从该列中清除第 10 - 20 行。我知道我必须创建一个函数，但我无法弄清楚该函数是什么。函数和循环在下面供您查看。

感谢您的帮助

Public Function ColumnLettersFromRange(rInput As Range) As String
    ColumnLettersFromRange = Mid(ActiveCell.Address, 2, InStrRev(ActiveCell.Address, "$") - 2)
End Function

dim RowDelete
Set RowDelete = Range("A9:Z9")
For Each cell in RowDelete
    if cell.value = "X" then (ColumnLettersFromRange & "10:" & 
    ColumnLettersFromRange & 20).clear
Next Cell

Answer 1

我认为这会做你想做的事(不需要创建函数)

Sub test1()
Dim RowDelete As Range, cell As Range
Set RowDelete = Range("A9:Z9")
    For Each cell In RowDelete
    If cell.Value = "X" Then Range(cell.Offset(1, 0), cell.Offset(11, 0)).Clear
    Next cell
End Sub

如果你想使用函数并坚持你的代码尝试(你的代码修改为工作)。你没有提供参数 rInput到函数并用 Range(...) 包含结果字符串.

Public Function ColumnLettersFromRange(rInput As Range) As String
    ColumnLettersFromRange = Mid(rInput.Address, 2, InStrRev(rInput.Address, "$") - 2)
End Function


    Sub test2()
    Dim RowDelete as Range, cell As Range
    Set RowDelete = Range("A9:Z9")
    For Each cell In RowDelete
        If cell.Value = "X" Then Range(ColumnLettersFromRange(cell) & "10:" & ColumnLettersFromRange(cell) & 20).Clear
    Next cell
    End Sub