Macro to delet the row having zero or negative numbers

[drniranjan]

Hi experts,
I have the data in two columns

12 1.3
43 2.7
11 0
32 4.3
-3.0 10.1
0 11
32 3.8
0 0
21 -12
11 21
-11.5 -2.3

Need to delet the entire the rows, having zero or negative values in the either of the coulmns or in both..

The result would look:

12 1.3
43 2.7
32 4.3
32 3.8
11 21

please help....it is urgent..

Thanks and regard,
N.

 

[Rick Rothstein]

Give this macro a try...

Sub DeleteZeroAndNegativeRows()
  Dim UnusedCol As Long, LastRow As Long
  Const StartRow As Long = 1
  Const Column1 As Long = 1
  Const Column2 As Long = 2
  Application.ScreenUpdating = False
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  UnusedCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Range(Cells(StartRow, UnusedCol), Cells(LastRow, UnusedCol))
    .FormulaR1C1 = "=IF(OR(RC[-" & (UnusedCol - Column1) & "]<=0,rc[-" & (UnusedCol - Column2) & "]<=0),1)"
    .SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Delete
    .Clear
  End With
  Application.ScreenUpdating = True
End Sub

Change the three constants (the Const statements) to match your actual setup.

 

[pgc01]

Hi

Quick solution: use a filter.

For ex., use autofilter and choose for the 2 fields:

is greater than 0

This gives you immediately your result table, that you can select and copy elsewhere.



Another option is the Advanced Filter, that allows you to copy the result as part of the filtering.