Delete Rows on Set Criteria
 

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
--
Many Thanks

Sue

Delete Rows on Set Criteria
 

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

"Sue" wrote:

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
--
Many Thanks

Sue

Delete Rows on Set Criteria
 

Sue,

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

Mike

"Sue" wrote:

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
--
Many Thanks

Sue

Delete Rows on Set Criteria
 

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
--
Many Thanks

Sue


"Barb Reinhardt" wrote:

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

"Sue" wrote:

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
--
Many Thanks

Sue

Delete Rows on Set Criteria
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sue" wrote in message
...
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
--
Many Thanks

Sue


"Barb Reinhardt" wrote:

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

"Sue" wrote:

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
--
Many Thanks

Sue

Delete Rows on Set Criteria
 

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

"Sue" wrote:

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
--
Many Thanks

Sue


"Barb Reinhardt" wrote:

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

"Sue" wrote:

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
--
Many Thanks

Sue

Delete Rows on Set Criteria
 

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

"Sue" wrote:

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
--
Many Thanks

Sue

Delete Rows on Set Criteria
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sue" wrote in message
...
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
--
Many Thanks

Sue


"Barb Reinhardt" wrote:

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

"Sue" wrote:

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
--
Many Thanks

Sue

Delete Rows on Set Criteria
 

Hi Everybody

Who replied thanks for your help

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


--
Many Thanks

Sue


"JLGWhiz" wrote:

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

"Sue" wrote:

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
--
Many Thanks

Sue