Solved: Delete everything but...

[blackie42]

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]

[vba]

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
[/vba]

[blackie42]

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]

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]

Thanks guys - works a treat now

regards

Jon