PDA

View Full Version : Solved: Delete everything but...



blackie42
12-14-2007, 08:52 AM
Hi,

How can I delete everything else (rows I mean) in a varying size sheet but leave rows intact based on the following

The word FUND in column B

The letter A in col C

Just to clarify - if word FUND appears on its own in col B leave the row, if the letter A appears in col C on its own leave row intact. All other rows being deleted.

Any help much appreciated

regards

Jon

Bob Phillips
12-14-2007, 09:11 AM
Public Sub ProcessData()
Dim i As Long
Dim LastItem As Long

With ActiveSheet

Application.ScreenUpdating = False
LastItem = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastItem To 1 Step -1
If .Cells(i, "A").Value <> "A" And .Cells(i, "B").Value <> "FUND" Then
.Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End With

End Sub

blackie42
12-14-2007, 09:29 AM
Hi,

Thanks for very quick response but code deletes everything but FUND really need to leave in the rows with A in second column.

I run it on attached test and all it leaves is fund

thanks

TXSkydiver
12-14-2007, 09:37 AM
Just change the column reference to C (where the "A" value is in the srpeadsheet you sent) instead of A.

Public Sub ProcessData()
Dim i As Long
Dim LastItem As Long

With ActiveSheet

Application.ScreenUpdating = False
LastItem = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastItem To 1 Step -1
If .Cells(i, "C").Value <> "A" And .Cells(i, "B").Value <> "FUND" Then
.Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End With

End Sub

blackie42
12-14-2007, 09:55 AM
Thanks guys - works a treat now

regards

Jon