Deleting Cells Range From Column Based On Header

Question

Just a note, I am new to VBA

I have a number of columns. If any column has the header "X", I want it to clear rows 10 - 20 from that column. I know I have to create a function but I cannot figure out what the function is. The function and the loop are below for you to see.

Thanks for any assistance

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

user_10405778 · Answer

i think this will do what you want to do (no need to create function)

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

if you want use function and stick to your code the try (your code modified to work). you hadn't supplied argument rInput to the function and encompassed resulting string with 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

user_6774472 · Answer

You could try:

Code:

Option Explicit

Sub test()

    Dim LastColumn As Long, i As Long

    With ThisWorkbook.Worksheets("Sheet1")

        'Let us assumne that headers appear in row number 1. Find the last column of row number 1
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

        For i = 1 To LastColumn

            If .Cells(1, i).Value = "Clear" Then
                .Range(.Cells(10, i), .Cells(20, i)).ClearContents
            End If

        Next i

    End With

End Sub

Results:

Note:

.Clear clear both values & formattings
.ClearContents clear only values

Deleting Cells Range From Column Based On Header

2 Answers