Macro to Delete Negative Values

J

jjsesler

New Member
Joined
Nov 11, 2009
Messages
8
Hi!

I have a spreadsheet with values that change each day which currently requires a lot of manual manipulation. Part of the manipulation involves removing from a row any negative value in which there is no preceeding positive amount.

For example, column A is blank, column B is blank, column C is -$100, column D is $100, column E is -$100. I need to remove the -$100 from column C but leave the other values. The problem with using a macro is the value in any given cell will not be the same from day to day, so telling it to delete the value in C3 won't always be right.

I do it now by filtering the first column on any value less than 0, delete all those items, then change the filter to blank, move to the next column and repeat, the next column repeat, etc.

Is there a macro that can be made smart enough to selectively delete data based on this logic? I've also tried to do this same thing in Access but am unable to get it to work there, either.

Thanks for any help.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Not clear to me whether or not 0 is to be treated as positive but based on my interpretation one possibility:

Code: 
Sub Example()
    Dim rngRow As Range, lngCol As Long, xlCalc As XlCalculation
    On Error GoTo ExitPoint
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    For Each rngRow In Sheets("Sheet1").UsedRange.Rows
        If Application.WorksheetFunction.CountIf(rngRow, ">=0") Then
            With rngRow
                lngCol = .Parent.Evaluate("MATCH(1,ISNUMBER(" & .Address & ")*(" & .Address & ">=0),0)")
                If lngCol > 1 Then .Cells(1).Resize(, lngCol - 1).ClearContents
            End With
        Else
            rngRow.ClearContents
        End If
    Next rngRow
ExitPoint:
    With Application
        .Calculation = xlCalc
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Modify sheet names etc... if 0 is to be treated as negative modify the operators as appropriate.
 
Upvote 0
Thank you for the reply!

I'm sorry, but I beg your indulgence for a noob. I'm quite good at basic Excel, but I'm new to Macros and VBA.

From the look of the code you gave me, it appears that I need to fill in the relevant values in that code to correspond to what I'm doing in my sheet? I can't just paste that into VBE and have it run correctly, right?
 
Upvote 0
All you need do (potentially) is modify "Sheet1" to be the name of the sheet against which this code is execute... thereafter based on the posts thus far I believe it will do what you want.

(test on a backup copy first of course)
 
Upvote 0
Thanks, but when I ran that code, it only deleted the header row of my data. :(

To hopefully make this a little easier, I recorded the macro of what I'm doing. Sorry, it's really long, and the problem I can see is that D3, E13, F10, etc, aren't always going to be the first cell in that column I need to select. Can I get around it by just putting D2, E2, F2, etc and then have it only select what's shown for deletion?

Code: 
Sub DELETE2()
'
' DELETE2 Macro
' Macro recorded 8/19/2010
'
'
    Range("D1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="<0", Operator:=xlAnd
    Range("D3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.ClearContents
    Range("A3").Select
    Selection.AutoFilter Field:=4, Criteria1:="="
    Selection.AutoFilter Field:=5, Criteria1:="<0", Operator:=xlAnd
    Range("E13").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.ClearContents
    Range("A13").Select
    Selection.AutoFilter Field:=5, Criteria1:="="
    Selection.AutoFilter Field:=6, Criteria1:="<0", Operator:=xlAnd
    Range("F10").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.ClearContents
    Range("A10").Select
    ActiveWindow.SmallScroll Down:=-18
    Selection.AutoFilter Field:=6, Criteria1:="="
    Selection.AutoFilter Field:=7, Criteria1:="<0", Operator:=xlAnd
    Range("G20").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.ClearContents
    Range("A20").Select
    Selection.AutoFilter Field:=7, Criteria1:="="
    Selection.AutoFilter Field:=8, Criteria1:="<0", Operator:=xlAnd
    Range("H102").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.ClearContents
    Range("A102").Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
MATCH function
2
Replies
14
Views
141
Fluff
Fluff
R
  • Question
Filter & Delete Deleting Header Row Suddenly
Replies
0
Views
233
reberryjr
R
Ashe
  • Solved
VBA to delete rows that DON'T contain certain text
Replies
4
Views
263
Joe4
Joe4
M
  • Solved
Delete a row on value
Replies
3
Views
190
michellin
M
E
  • Question
Subtotal values and then hide rows
Replies
3
Views
126
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,097
Messages
6,123,076
Members
449,094
Latest member
mystic19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top