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?
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?