VBA for deleting cells

[kd123]

Hello,

I am looking for a VBA code that will delete all cells with a value less than 0.01 and shift cells up for column A and then to repeat the process for column C.

Thank you in advance for the help. I really appreciate it.

 

[alansidman]

Option Explicit


Sub DelAC()
    Dim rng As Range, rng2 As Range, c As Range
    Dim lrA As Long, lrC As Long
    lrA = Range("A" & Rows.Count).End(xlUp).Row
    lrC = Range("C" & Rows.Count).End(xlUp).Row
    Set rng = Range("A1:A" & lrA)
    Set rng2 = Range("C1:C" & lrC)
    Application.ScreenUpdating = False
    For Each c In rng
        If c < 0.01 Then
            c.Delete
        End If
    Next c
    For Each c In rng2
        If c < 0.01 Then
            c.Delete
        End If
    Next c
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

 

[Fluff]

Maybe


Sub kd_123()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("if(" & .Address & "<0.01,true," & .Address & ")")
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).Delete xlUp
      On Error GoTo 0
   End With
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Value = Evaluate("if(" & .Address & "<0.01,true," & .Address & ")")
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).Delete xlUp
      On Error GoTo 0
   End With
End Sub

 

[kd123]

Thank you so much for the help! I cannot tell you how much I appreciate it.

 

[kd123]

Thank you so much for the help! I cannot tell you how much I appreciate it!

 

[Fluff]

Glad we could help & thanks for the feedback