CODEWITHSUNDEEP
vbaexcel
Joey Z
Joey Z

Reputation: 3

Deleting Negative Values from Excel (VBA)

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

Upvotes: 0

Views: 2296

Answers (2)

user4039065
user4039065

Reputation:

.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.

Upvotes: 1

user8753746
user8753746

Reputation:

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

Upvotes: 0

Related Questions