Excel - 2003 - Delete data if conditions met

[Arceedee]

Hi
I'm writing a small program that cleanses data in a s/s. A formula advises
whether in each row the result is true or false. I only need to keep the
true data so I'm looking for a way to delete the false results.
If it helps, the way I'm finding whether true or false is by formula which
uses ISNUMBER search for certain criteria to be met i.e. whether the data in
the cell contains one of 6 given UK postcodes.
(=OR(ISNUMBER(SEARCH({"BD","HX","HD","LS","WF","OL"},H2)))

 

[OssieMac]

When you say "delete the false results" do you mean to delete the row or
simply clear the contents?

 

[Arceedee]

I need to delete the rows. When I get my true/false results I have been
sorting so all false are together then manually deleting the rows. What I'd
like to do is create a formula or macro that will do this automatically.

 

[OssieMac]

Following macro will delete the rows for you. However, ensure you make a
backup of your workbook before implementing it in case it does not do exactly
as you expect.

Sub DeleteRows()

Dim rngToTest As Range
Dim columnId As String
Dim rowStart As Long
Dim i As Long

'Edit K in following line to match the column with True/False
columnId = "K"

'Edit 2 in the following line to match the first row of data
rowStart = 2

'Edit Sheet1 to match your sheet name
With Sheets("Sheet1")
Set rngToTest = Range(.Cells(rowStart, columnId), _
.Cells(.Rows.Count, columnId).End(xlUp))
End With


With rngToTest
'Must work backwards from bottom when deleting rows
For i = .Rows.Count To 1 Step -1
If .Cells(i, 1) = False Then
.Cells(i, 1).EntireRow.Delete
End If
Next i
End With

End Sub

 

[Arceedee]

I'm pretty new to macros. I'm OK doing the physical creation but am I
correct in saying that I need to start one then step into it and paste your
macro as edited? Not sure I've got it right but first row is 2 and column
with "false" is J
'Sub DeleteRows()

Dim rngToTest As Range
Dim columnId As String
Dim rowStart As Long
Dim i As Long

With Sheets("New")
Set rngToTest = Range(.Cells(rowStart = 2, columnId = J), _
..Cells(.Rows.Count, columnId = J).End(xlUp))
End With

With rngToTest
'Must work backwards from bottom when deleting rows
For i = .Rows.Count To 1 Step -1
If .Cells(i, 1) = False Then
..Cells(i, 1).EntireRow.Delete
End If
Next i
End With

End Sub

 

[willy]

My preference would be to use AUTO FILTER, depends on deleted vs hidden, but
code and macros seem to always induce heartburn, sometime somewhere.

Cheers Willy

 

[OssieMac]

You deleted the rows where the column Id and row number are assigned to
variables. Anyway with the info you have given me, the following is what you
need.

Make a backup copy of your workbook in case the code does not do exactly
what you want.

Open the VBA editor by holding the Alt key and pressing F11.

Click on menu item Insert then select Module.

Copy all of the code below from Sub DeleteRows() to End Sub into the large
white area of the VBA editor.

Sub DeleteRows()

Dim rngToTest As Range
Dim columnId As String
Dim rowStart As Long
Dim i As Long

columnId = "J"

rowStart = 2

With Sheets("New")
Set rngToTest = Range(.Cells(rowStart, columnId), _
.Cells(.Rows.Count, columnId).End(xlUp))
End With


With rngToTest
'Must work backwards from bottom when deleting rows
For i = .Rows.Count To 1 Step -1
If .Cells(i, 1) = False Then
.Cells(i, 1).EntireRow.Delete
End If
Next i
End With

End Sub

Close the VBA editor by clicking the red X top right then save the workbook.

Lookup in Help on how to run the macro from the menu. If you let me know
what version of xl you have then I will provide instructions on how you can
install a button to run the macro.

You also have a suggestion to use Autofilter. Might be worth checking out
Help on how to do it that way. You simply set autofilter to false and select
all the rows and elete them. Even if you decide not to use Autofilter here,
it is an invaluable tool that you will certainly use at a later date.

 

[Arceedee]

OssieMac, That's pretty good. Works fine except that with up to 12000 rows
to delete it takes quite a while. I've tried Willy's suggestion and,
building it into a macro it's instant.
I really do appreciate all your assistance.
Cheers.

 

[Arceedee]

Willy, Many thanks. I did like OssieMac's suggestion. Simple is good for me.
Cheers
Arceedee