Deleting cell contents which contain negative values

[e_sauven]

Hi

I'm trying to clear the content of cells in a large table that contain a negative value using some VBA.

Any help greatly appreciated.

Thanks

Emilie

 

[JackDanIce]

You'll need to change the red parts in the code to fit your requirements but try:

Sub Macro1()

Dim i As Long

With ActiveSheet
    .AutoFilterMode = False
    i = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A1:J" & i).AutoFilter
    .Range("A1:J" & i).AutoFilter Field:=5, Criteria1:="<0"
    .Range("A1:J" & i).Offset(1).Resize(i - 1).SpecialCells(xlCellTypeVisible).ClearContents
    .AutoFilterMode = False
End With

End Sub

 

[VBA Geek]

Sub RemCellWithNegVal()
    Dim iRange As Range
    On Error Resume Next
    Set iRange = Application.InputBox("Select the Range", , , , , , , 8)
    If iRange Is Nothing Then Exit Sub
    On Error GoTo 0
     iRange = Evaluate("IF(" & iRange.Address & "<0, """", " & iRange.Address & ")")
End Sub

 

[VBA Geek]

to be complete,

here's a version that wont return annoying 0's if blank cells are found within your selected range

Sub RemCellWithNegVal()
    Dim iRange As Range
    On Error Resume Next
    Set iRange = Application.InputBox("Select the Range", , , , , , , 8)
    If iRange Is Nothing Then Exit Sub
    On Error GoTo 0
    iRange = Evaluate("IF(" & iRange.Address & "="""","""",IF(" & iRange.Address & "<0,"""",A1:E18))")
end sub

 

[Peter_SSs]

Possibly this?

Sub DeleteNegatives()
  Range("A2:J11").Replace What:="-*", Replacement:="", LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=False
End Sub

Which raises the issue of whether you really need a macro or could possibly just use the Find/Replace method manually?

 

[e_sauven]

Possibly this?

Sub DeleteNegatives()
  Range("A2:J11").Replace What:="-*", Replacement:="", LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=False
End Sub

Which raises the issue of whether you really need a macro or could possibly just use the Find/Replace method manually?

Thanks that's fantastic, just what I needed

Really appreciate it, thanks all

Emilie

 

[e_sauven]

thanks very much for your help!

 

[Peter_SSs]

Thanks that's fantastic, just what I needed

Really appreciate it, thanks all

Emilie

Glad to help. Thanks for letting us know.