Delete Rows on Set Criteria

[Sue]

Hi All

An easy one for all you experts

I need a macro to delete the all the rows on the active sheet if in Column F
/ Row5 the values in all the cells in Column F do not match the value in
Column F / Row5

 

[Mike H]

Sue,

That doesn't make sense, what are we comparing Column F to

Mike

 

[Barb Reinhardt]

All the cells in Row F? Do you mean all 256 in Excel 2003 and who knows
how many in 2007? Or are you just talking about the cells in the UsedRange?

Barb Reinhardt

 

[Shasur]

Hi Sue

Which two sheets are you comparing ?

Here is a hint

Sub Delete_Rows_ForSue()


Dim iRow
Dim iMaxRow

iMaxRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
For iRow = 1 To iMaxRow
If iRow > iMaxRow Then Exit Sub
If ActiveSheet.Cells(iRow, 6) <> Sheets(2).Cells(iRow, 6) Then
Rows(iRow).EntireRow.Delete
iRow = iRow - 1
iMaxRow = iMaxRow - 1
End If
NoDelete:
Next iRow
End Sub

Cheers
Shasur

 

[Mike H]

I bet you never tested this

Hi Sue

Which two sheets are you comparing ?

Here is a hint

Sub Delete_Rows_ForSue()


Dim iRow
Dim iMaxRow

iMaxRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
For iRow = 1 To iMaxRow
If iRow > iMaxRow Then Exit Sub
If ActiveSheet.Cells(iRow, 6) <> Sheets(2).Cells(iRow, 6) Then
Rows(iRow).EntireRow.Delete
iRow = iRow - 1
iMaxRow = iMaxRow - 1
End If
NoDelete:
Next iRow
End Sub

Cheers
Shasur

 

[Sue]

Hi

My mistake

I should have stipulated the used range

and in Column F / Row 5 there is a number and if any cell in the used range
in Column F does not match that number then the row is deleted

 

[Barb Reinhardt]

A better way would be

For iRow = iMaxRow to 1 step -1

And don't update iRow or iMaxRow later.

Barb Reinhardt

 

[Don Guillett]

If you have col f with
f1
f2
f3
f4
f5 10
f6 4
f7 5
f8 10
f9 6
f10 10
and you want to delete row 8 and row 10, then this will do it

for i = 10 to 6 step -1
if cells(i,"f")=cells(5,"f") then rows(i).delete
next i

 

[Mike H]

Maybe

Sub copyit()
Dim MyRange, MyRange1 As Range, DeleteRange As Range
myvalue = Range("F5").Value
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set MyRange = Range("F6:F" & lastrow)
For Each c In MyRange
If c.Value <> myvalue Then
If DeleteRange Is Nothing Then
Set DeleteRange = c.EntireRow
Else
Set DeleteRange = Union(DeleteRange, c.EntireRow)
End If

End If
Next
If Not DeleteRange Is Nothing Then
DeleteRange.Delete
End If
End Sub

Mike

 

[Don Guillett]

See my post

 

[JLGWhiz]

Hi Sue, I think this is what you are looking for.

Sub delRws()
Dim lstRw As Long, i As Long
lstRw = ActiveSheet.Cells(Rows.Count, 6).End(xlUp).Row
For i = lstRow To 6 Step - 1
If ActiveSheet.Cells(i, 6) <> ActiveSheet.Range("F5") Then
ActiveSheet.Cells(i, 6).EntireRow.Delete
End If
Next
End Sub

 

[Don Guillett]

To delete rows 5,7,9 then use
if cells(i,"f")<>cells(5,"f") then rows(i).delete

 

[Mike H]

Don,

Most do this row deleting in reverse and delete line by line for reasons we
understand but I prefer going forward through the range and doing a single
delete on a union of ranges at the end. Are there any compelling reasons for
preferring one way over the other or is it simply a matter of preference?

Mike

 

[Sue]

Hi Everybody

Who replied thanks for your help

Eventually used JLGWhiz's solution after I found the typo error

 

[Don Guillett]

Mike,
I have seen it done both ways and even tho I haven't tested I suspect it is
better with a large sheet. These days most things are fast anyway.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Don,

Most do this row deleting in reverse and delete line by line for reasons
we
understand but I prefer going forward through the range and doing a single
delete on a union of ranges at the end. Are there any compelling reasons
for
preferring one way over the other or is it simply a matter of preference?

Mike