Solved How do I select all negative cells in a column

September 19, 2012 at 20:12:30
Specs: Windows 7
How do I select all negative cells in a column

See More: How do I select all negative cells in a column

Report •


#1
September 20, 2012 at 04:53:00
The answer depends on what you want to do with them.

Why not tell us your overall goal and we'll see what help we can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 20, 2012 at 19:01:19
I have the following code to select all negative values in a column (column H). because I need to see the total of the negative amount which appears on the lower right corner of the sheet to have an estimate of how much I need but the problem is it only works if there are about 66 rows and I'm getting an error since the row of my data is too large.
Not all data on the column are negative and there are almost 2K rows as of now and I have a lot of sheet to look at and I need to do this quick.

Sub BatchTotal_H()
Dim Row As Integer
Dim CellsToSelect As String
For Row = 1 To Range("H" & CStr(Rows.Count)).End(xlUp).Row
If Range("H" & CStr(Row)).Value < 0 Then
If CellsToSelect <> "" Then CellsToSelect = CellsToSelect & ","
CellsToSelect = CellsToSelect & "H" & CStr(Row)
End If
Next Row
Range(CellsToSelect).Select
End Sub


Report •

#3
September 20, 2012 at 22:14:53
✔ Best Answer
First, a posting tip:

Before posting code in this forum, please click on the blue line at the end of this post and read this instructions found via that link.

As for your question, why use the value in the status bar? Selecting the cells just to present a number seems pretty inefficient. Why not use code to SUM the negative values and present the number in a message box?

Sub BatchTotal_H()
Dim Row, negSum As Integer
  For Row = 1 To Range("H" & CStr(Rows.Count)).End(xlUp).Row
    If Range("H" & CStr(Row)) < 0 Then
      negSum = negSum + Range("H" & CStr(Row))
    End If
  Next Row
 MsgBox negSum
End Sub

Easier yet, why not just use a formula in a cell?

=SUMIF(H:H,"<0")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question