Macro to delete rows
NG wrap-around. Try this instead
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 3 Step -1
If .Cells(i, "A").Value = .Cells(i - 1, "A").Value And _
.Cells(i, "B").Value = .Cells(i - 1, "B").Value And _
.Cells(i, "C").Value = .Cells(i - 1, "C").Value And _
.Cells(i, "D").Value = .Cells(i - 1, "D").Value And _
Abs(.Cells(i, "I").Value) = _
Abs(.Cells(i - 1, "I").Value) Then
.Rows(i).Delete
End If
Next i
End With
End Sub
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Tasha" wrote in message
...
Thanks Bob!!! When I run this though, I am getting a syntax error??? What
would cause that?
"Bob Phillips" wrote:
I have a few more left than you
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 3 Step -1
If .Cells(i, "A").Value = .Cells(i - 1, "A").Value And _
.Cells(i, "B").Value = .Cells(i - 1, "B").Value And _
.Cells(i, "C").Value = .Cells(i - 1, "C").Value And _
.Cells(i, "D").Value = .Cells(i - 1, "D").Value And _
Abs(.Cells(i, "I").Value) = Abs(.Cells(i - 1, "I").Value)
Then
.Rows(i).Delete
End If
Next i
End With
End Sub
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"Tasha" wrote in message
...
I have a spreadsheet that is updated daily. I need a macro that will
check
data for each record, in columns A,B,C and D, if they are the same, and
the
data in Column I has a positive and negative amount that are the same,
delete
both rows. See Example Below:
ROOM PATNO PATNAME CNSDAY DAYS F_C HSV QTY
AMT
ICU1 167 SCHMOE JOE 11 1 M MED
1 953.80
ICU1 167 SCHMOE JOE 12 2 M ICU
1 953.80*
ICU1 167 SCHMOE JOE 13 3 M MED
1 1293.00
ICU1 167 SCHMOE JOE 12 2 M ICU
1 953.80*
ICU1 167 SCHMOE JOE 12 2 M ICU
1 (953.80)*
ICU1 167 SCHMOE JOE 12 2 M MED
1 953.80
(would need it to delete the 2nd and 3rd rows, with everything matching
in
columns A,B,C and D, and ONE debit and credit for the same amount needs
rows
deleted....if not same info and amounts, rows not deleted.
would end up looking like this:
ROOM PATNO PATNAME CNSDAY DAYS F_C HSV QTY
AMT
ICU1 167 SCHMOE JOE 11 1 M MED
1 953.80
ICU1 167 SCHMOE JOE 13 3 M MED
1 1293.00
Is there any way to do this? I'm currently doing it manually every day
using filters, then deleting....is very time consuming!
|