Deleting Negative Values from Excel (VBA)

Question

First time posting on this website. I request some much needed help with a small project I am working with.

I need to remove all negative numbers past a certain cell which is determined by today's date. To give some background, Cell G3 is a formula that uses an HLOOKUP to determine the column of data that corresponds with today's date. I need to delete all negative numbers to the right of that column (which are future dates) and are also below row 9. The VBA code below is not working and I would greatly appreciate some assistance from you guys. I do not use VBA often and this is one of my first projects in it.

2 Edits in Bold Question: I would like to delete negative numbers from rows succeeding row 9 and columns succeeding the column number in Cell G3

The Code does not work - meaning that the code does not result in any errors or anything, it simply runs but does not delete negative numbers succeeding row 9 and the column number within Cell G3

Below is what I have so far:

Sub Negative_to_Zero()

Dim RowNum As Long, ColNum As Long, H3 As Integer
H3 = Range("G3").Value

Application.ScreenUpdating = False
For ColNum = H3 To Cells(1, Columns.Count).End(xlToLeft).Column
  For RowNum = 9 To Cells(Rows.Count, ColNum).End(xlUp).Row
     If Cells(RowNum, ColNum) < 0 Then Cells(RowNum, ColNum) = 0
  Next RowNum
Next ColNum
Application.ScreenUpdating = True

End Sub

"The VBA code below is not working" - What is not working? Does it crash? Does it not do what you are expecting? Something else? Is it something as simple as it is deleting the stuff from the column corresponding with today, instead of starting one column to the right (i.e. you used H3 instead of H3 + 1 (and similarly 9 instead of 9 + 1)? — YowE3K
– YowE3K, Commented Nov 27, 2017 at 19:14
And why is there an [access-vba] tag on the question? How does this relate to Access? Are you trying to perform this task within Access VBA, using an Excel Application object to get to the Excel workbook? — YowE3K
– YowE3K, Commented Nov 27, 2017 at 19:20
Welcome to SO, I could not find a clear question, can you modify your post and define a question to solve? Please read> How to Ask — user8753746
– user8753746, Commented Nov 27, 2017 at 19:20
Sorry! I should clarify. The code does not do anything to the dataset. Everything seems to be unchanged. I do not want to delete stuff from the columns to the right only, I would like to delete negative values to the columns preceding it. — Joey Z
– Joey Z, Commented Nov 27, 2017 at 19:21

user4039065 · Accepted Answer · 2017-11-27 19:44:30Z

1

.Find the last row and last column by looking backwards from A1. Read all of the values into a variant array and cycle through the array, clearing negative values as you go. Return the array's values to the worksheet.

Option Explicit

Sub wqew()
    Dim lr As Long, lc As Long
    Dim vals As Variant, i As Long, j As Long

    With Worksheets("sheet1")
        lr = .Cells.Find(What:=Chr(42), After:=.Cells(1), _
                         SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lc = .Cells.Find(What:=Chr(42), After:=.Cells(1), _
                         SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        With .Range(.Cells(9, "T"), .Cells(lr, lc))
            vals = .Value2
            For i = LBound(vals, 1) To UBound(vals, 1)
                For j = LBound(vals, 2) To UBound(vals, 2)
                    If vals(i, j) < 0 Then
                        vals(i, j) = vbNullString
                    End If
                Next j
            Next i
            .Value = vals
        End With
    End With
End Sub

The logic for determining negative values does not error check for text and/or errors; it assumes that numeric values are in the cells. It also does not check for formulas returning a numeric result; if you have formulas and want to keep them then it would be better to remove the array and use the slower cell-by-cell method of clearing negative values.

answered Nov 27, 2017 at 19:44

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

YowE3K Over a year ago

"T" need to be .Range("G3").Value+1 (I used "T" in some comments as an example only) And 9 probably(?) needs to be 10.

Joey Z Over a year ago

This works perfect! Thanks for your help YoeE3K and Jeeped. Appreciate the logic behind it as well.

user8753746 · Accepted Answer · 2017-11-27 19:24:19Z

0

Try to modify this code that reads each cell of a Range:

Dim rng As Range: Set rng = Application.Range("Data!B2:C3") ' Here you can declare your range.
Dim cel As Range
For Each cel In rng.Cells
    With cel
        'Your If statement Here.
    End With
Next cel

answered Nov 27, 2017 at 19:24

user8753746

Collectives™ on Stack Overflow

Deleting Negative Values from Excel (VBA)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related