Clear Multiple Cells Or Ranges On A Worksheet

less than 1 minute read

This joins many addresses together as a string, picks up a defined named range “sNamedRange” and clears the contents from the cells.

'==================================================================================================
' ## Clear data from multiple ranges
'==================================================================================================
Sub ClearReportData()

    ' // vars
    Dim sNamedRange As String
    sNamedRange = Range("sNamedRange").Address

    Dim strCells As String
    strCells = "E6:E8, B14:C18, E14:G18, I14:J14, L14:N14, E23:E25, " & _
               "B31:C35, E31:G35, I31:J35, L31:N35, E40:E42, " & _
               "B48:C52, E48:G52, I48:J52, L48:N52"

    ' // clear the Named Range and the string of addresses
    With Worksheets("Sheet1")
        .Range(strCells).ClearContents
        .Range(sNamedRange).ClearContents
    End With

End Sub

Tags:

Categories:

Updated: