CODEWITHSUNDEEP
excelvba
aks11111
aks11111

Reputation: 3

VBA to erase formulas in certain range of cells if returning blank or ""?

I have formulas throughout a workbook that are returning an output if matched with X and "" if not. I am new to VBA and macro's and unsure of where to begin. But my goal is to have a macro that I could run that clears the formula if it is blank or "" across multiples sheets. I would note, I only want it to do this in certain columns of each sheet.

Example:
Sheet 1 has the formula in cells H10:K20, while Sheet 2 has the formula in AV8:AV400, etc. etc. The goal being to have it recognize "Sheet 1" is a range of H10:K20 where it would erase, Sheet 2 is AV8:AV400.

Any help would be greatly appreciated!

I had found another question that was kind of similar, but I could not figure out how to make it recognize different sheet names or specific ranges within my file. I have pasted the code I had found and tried to use below as well as the link here.
How to clear cell if formula returns blank value?

Sub ClearCell()
    Dim Rng As Range
    Set Rng = ActiveSheet.Range("A1")

    Dim i As Long
    For i = 1 To 10
        If Rng.Cells(i,1) = "" Then
           Rng.Cells(i,1).ClearContents
        End If
    Next i
End Sub

Upvotes: 0

Views: 1420

Answers (1)

JvdV
JvdV

Reputation: 75990

Maybe something very basic:

Sub ClearEmptyFormulas()

Dim ws As Worksheet
Dim rng As Range, cl As Range

For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")) 'Extend the array or loop all worksheets
    Select Case ws.Name
        Case "Sheet1"
            Set rng = ws.Range("H10:K20")
        Case "Sheet2"
            Set rng = ws.Range("AV8:AV400")
        'Etc
    End Select
    For Each cl In rng
        If cl.Value = "" Then
            cl.ClearContents
        End If
    Next cl
Next ws

End Sub

Or even a bit simpler:

Sub ClearEmptyFormulas()

Dim rng As Range, cl As Range
Dim arr1 As Variant: arr1 = Array("Sheet1", "Sheet2")
Dim arr2 As Variant: arr2 = Array("H10:K20", "AV8:AV400")

For x = 0 To 1
    Set rng = Sheets(arr1(x)).Range(arr2(x))
    For Each cl In rng
        If cl.Value = "" Then
            cl.ClearContents
        End If
    Next cl
Next x

End Sub

Upvotes: 0

Related Questions