Row/Column Data Clear - Excel VBA

[tbassngal]

Hi all, I am new to Excel VBA and haven't a clue how to get started here. I have the following spreadsheet:

ColA, ColB, ColC, ColD, ColE
PID #, Type, Density, Expenditure1, Expenditure2
116211, NEW, 30, $130,943.46, $295,834.92
116211, Modify, 0, $130,943.46, $295,834.92
116211, Unk, 0, $130,943.46, $295,834.92
116212, Modify, 0, $130,943.46, $295,834.92
116212, NEW, 12, $130,943.46, $295,834.92
116212, Unk, 0, $130,943.46, $295,834.92
116231, NEW, 100, $630,563.57, $316,575.00
116231, Modify, 0, $630,563.57, $316,575.00
116231, Unk, 0, $630,563.57, $316,575.00

What I would like to do is look at the PID # and Density >0 - if true, then I would like to keep the data in ColD and ColE (most times this is associated with type of NEW. If not true (Density<0) and PID is the same as the value in the above row but Density <0 then I would like to delete the value in ColD and ColE to prevent duplicate information.

Sub RemoveStuff()
Dim i As Long
i = 3
While Not IsEmpty(Cells(i, 1))
If Cells(i, 1).Value = Cells(i - 1, 1).Value And Cells(i, 3) <=0 Then _
Range(Cells(i, 4), Cells(i, 5)).Clear
i = i + 1
Wend
End Sub

The above code works unless (in the case of PID #116212)... the first row (PID #) checked is a row where Density <=0, meaning the next PID # is the one with Density being >0 - any suggestions?

 

[Bong]

You don't say what to do if the first row does have Density <=0. Assuming you don't do anything in that case (that seems to imply that the New record will always follow an existing record if there is one), you could simply start at row 4.

_________________
Bob Rashkin

 

[tbassngal]

Bong: if the density is <=0 then the information in columns is cleared. The idea here is that if it is the same PID number, then I don't want to see duplicate Expenditure costs for items with density <=0, only for items with density >=0 with the same PID #. Does this help? Starting at row 4 doesn't really help me in this case? Any other observations that may help would greatly be appreciated! Thank you!

Tina

 

[Bong]

I still don't understand. Why are you checking for repeating PIDs if you want to clear non-positive density fields anyway?

_________________
Bob Rashkin