Remove Or Delete Rows Having Zero Or Negative Values In A Column

[tinamiller1]

I am trying to copy only the rows that have a value that is positive or negative but no 0.00 or $- values.
Here is my macro:




'macro eight
'move the s&h implants
Sub moveIMPShipping()
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False

If Range("o2:o1847") <> 0 Then
For Each Area In Sheets("Summary").Range("g2", Range("g" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeCons tants).Areas
With Area
sr = .Row
er = sr + .Rows.Count - 1
Sheets("Summary").Range("e" & er + 1).Value = Sheets("Summary").Range("n" & sr).Value
Sheets("Summary").Range("l" & er + 1).Value = Sheets("Summary").Range("o" & sr).Value
Sheets("Summary").Range("m" & er + 1).Value = Sheets("Summary").Range("o" & sr).Value

End With

Next Area
Sheets("Summary").Columns.AutoFit
Application.ScreenUpdating = True
End If
End Sub


DATA HAVE
claimnumber mpin dos auditdt impcomponent impunit$ impextnd$ impSH impSH$
00111 129819 8/27/2012 7/24/2013 Rod $320.32 $640.64 S&H Total: $-
Screw - Polyaxial $1,609.61 $4,828.83
Screw - Polyaxial $1,609.61 $3,219.22
$-
$-
00222 2563663 3/28/2012 7/17/2013 Pacemaker $17,250.00 $17,250.00 S&H Total: $-
Pacing Lead $-
Pacing Lead $-
$-
$-
00333 378634 8/27/2012 8/22/2013 Putty $750.00 $750.00 S&H Total: $30.00
Allograft $3,500.00 $7,000.00
Screw $300.00 $1,200.00
Locking Cap $250.00 $250.00
Screw $150.00 $150.00
Cage $3,000.00 $3,000.00
Plate $3,000.00 $3,000.00
Screw $1,150.00 $4,600.00
Screw $100.00 $400.00
Rod $200.00 $400.00
Bone Chips $175.00 $175.00
$-
$-
$-

00444 2674638 6/13/2012 8/14/2013 Stent $625.00 $625.00 S&H Total: $-
$-
$-
00555 922880 11/15/2011 7/26/2013 Blocker $1,604.47 $1,604.47 S&H Total: $10.00


DATA NEED
claimnumber mpin dos auditdt impcomponent impunit$ impextnd$ impSH impSH$
00111 129819 8/27/2012 7/24/2013 Rod $320.32 $640.64 S&H Total: $-
Screw - Polyaxial $1,609.61 $4,828.83
Screw - Polyaxial $1,609.61 $3,219.22
$-
$-
00222 2563663 3/28/2012 7/17/2013 Pacemaker $17,250.00 $17,250.00 S&H Total: $-
Pacing Lead $-
Pacing Lead $-
$-
$-
00333 378634 8/27/2012 8/22/2013 Putty $750.00 $750.00 S&H Total: $30.00
Allograft $3,500.00 $7,000.00
Screw $300.00 $1,200.00
Locking Cap $250.00 $250.00
Screw $150.00 $150.00
Cage $3,000.00 $3,000.00
Plate $3,000.00 $3,000.00
Screw $1,150.00 $4,600.00
Screw $100.00 $400.00
Rod $200.00 $400.00
Bone Chips $175.00 $175.00
S&H Total: $30.00 $30.00
$-
$-

00444 2674638 6/13/2012 8/14/2013 Stent $625.00 $625.00 S&H Total: $-
$-
$-
00555 922880 11/15/2011 7/26/2013 Blocker $1,604.47 $1,604.47 S&H Total: $10.00
S&H Total: $10.00 $10.00









Well, the format of the data I have and need did not post right and I cannot find on here how to edit my post and attach the worksheet.

[tinamiller1]

Since the edit does not allow me to attach my data sheet I had to reply. I hope this does not deter others from replying.

[Admin]

Hi

Try this


Sub moveIMPShipping()

Dim Area As Range, sr As Long, er As Long
Dim Sht As Worksheet

Application.ScreenUpdating = False

Set Sht = Worksheets("Sheet1") '<< adjust sheet name
'adjust the e3 in actual sheet
For Each Area In Sht.Range("e3", Sht.Range("e" & Sht.Rows.Count).End(xlUp)).SpecialCells(xlCellType Constants).Areas
With Area
If Not .Cells(1).Offset(, 4).Value = 0 Then
sr = .Row
er = .Rows.Count
.Cells(er + 1, 1).Value = .Cells(1).Offset(, 3).Value
.Cells(er + 1, 2).Resize(, 2).Value = .Cells(1).Offset(, 4).Value
.Cells(er + 1, 2).Resize(, 2).NumberFormat = .Cells(er, 2).Resize(, 2).NumberFormat
End If
End With
Next Area

Sht.Columns.AutoFit
Application.ScreenUpdating = True

End Sub

[snb]

I don't see any difference in what you 'have' and what you 'need'.

you can use


sub M_snb()
columns(7).specialcells(2).offset(2).specialcells( 4).entirerow.delete
columns(7).specialcells(2).offset(2).specialcells( 2,2).entirerow.delete
End Sub