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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.