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

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

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

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
--
http://vbadud.blogspot.com


"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

I bet you never tested this

"Shasur" wrote:

> 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
> --
> http://vbadud.blogspot.com
>
>
> "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

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

A better way would be

For iRow = iMaxRow to 1 step -1

And don't update iRow or iMaxRow later.

Barb Reinhardt



"Shasur" wrote:

> 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
> --
> http://vbadud.blogspot.com
>
>
> "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

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
(E-Mail Removed)
"Sue" <(E-Mail Removed)> wrote in message
news:1E2804B2-AC9F-45B9-8415-(E-Mail Removed)...
> 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

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

See my post

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Shasur" <(E-Mail Removed)> wrote in message
news:7D03ED67-0BD6-45DA-A604-(E-Mail Removed)...
> 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
> --
> http://vbadud.blogspot.com
>
>
> "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