VBA/Macros - How to auto delete data from cell if cell begins with

[egarcia]

I would like to see if someone can help me with a macros formula. I need to delete cell values if a cell begins with a certain number.

Delete all cell values for B22 through D65536 IF cell in C22 through C65536 begins with 3.00, 4.00, 5.00 AND Delete all cell values for G22 through I65536 IF cell in H22 through H65536 begins with 3.00, 4.00, 5.00

 

[JLGWhiz]

Here is one way:

Sub delStuff()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells.Find("*", sh.Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
    For i = lr To 22 Step -1
        With sh
            If Left(Trim(.Cells(i, 3).Value), 4) = CDbl("3.00") Or Left(Trim(.Cells(i, 3).Value), 4) = CDbl("4.00") Or _
            Left(Trim(.Cells(i, 3).Value), 4) = CDbl("5.00") Then
                .Cells(i, 2).Resize(1, 3).ClearContents
            End If
            If Left(Trim(.Cells(i, 8).Value), 4) = CDbl("3.00") Or Left(Trim(.Cells(i, 8).Value), 4) = CDbl("4.00") Or _
            Left(Trim(.Cells(i, 8).Value), 4) = CDbl("5.00") Then
                .Cells(i, 7).Resize(1, 3).ClearContents
            End If
        End With
    Next
End Sub

If your values in the columns to be cleared are derived from formulas, this will remove your formulas. Also, this assumes the cells contain data type numbers in columns C and G.

 

[egarcia]

thanks, this worked!

Here is one way:

Sub delStuff()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells.Find("*", sh.Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
    For i = lr To 22 Step -1
        With sh
            If Left(Trim(.Cells(i, 3).Value), 4) = CDbl("3.00") Or Left(Trim(.Cells(i, 3).Value), 4) = CDbl("4.00") Or _
            Left(Trim(.Cells(i, 3).Value), 4) = CDbl("5.00") Then
                .Cells(i, 2).Resize(1, 3).ClearContents
            End If
            If Left(Trim(.Cells(i, 8).Value), 4) = CDbl("3.00") Or Left(Trim(.Cells(i, 8).Value), 4) = CDbl("4.00") Or _
            Left(Trim(.Cells(i, 8).Value), 4) = CDbl("5.00") Then
                .Cells(i, 7).Resize(1, 3).ClearContents
            End If
        End With
    Next
End Sub

If your values in the columns to be cleared are derived from formulas, this will remove your formulas. Also, this assumes the cells contain data type numbers in columns C and G.