What code should I use if I want to delete entire Rows (and then move all
other rows below up) if the value in say Sheet1 A1:A.... is not within a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as
they will change in Row numbers daily - so I would want the code to search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks

Hi John
This code will scan your entire list in column A on sheet1 and check if the
value found is in the list in column A on sheet2
If the value on sheet1 is not found on sheet2 then the entire row on sheet1
is deleted and all rows shifted up.
It adjusts for varying lengths of data in both sheet1 and sheet2.

Public Sub SelectiveDelete()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

Cheers
Nigel

"John" wrote in message
...
What code should I use if I want to delete entire Rows (and then move all
other rows below up) if the value in say Sheet1 A1:A.... is not within a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as
they will change in Row numbers daily - so I would want the code to search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks

Thanks Nigel, I have several 'lists' in Sheet1 Column A that do not relate
to the search I am doing. In saying that I cannot specify a set range as my
'source' range can change. Would I be better specifying a Range name for my
'source' data


"Nigel" wrote in message
...
Hi John
This code will scan your entire list in column A on sheet1 and check if
the
value found is in the list in column A on sheet2
If the value on sheet1 is not found on sheet2 then the entire row on
sheet1
is deleted and all rows shifted up.
It adjusts for varying lengths of data in both sheet1 and sheet2.

Public Sub SelectiveDelete()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

Cheers
Nigel

"John" wrote in message
...
What code should I use if I want to delete entire Rows (and then move
all
other rows below up) if the value in say Sheet1 A1:A.... is not within a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1 A1:A....
as
they will change in Row numbers daily - so I would want the code to
search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks

Hi John
It depends on how you are loading this list. Range names are a good way of
limiting the range extent and simplify coding, but if your range extent is
changing you will need to use VBA to control the addition or subtraction
to/from the range, but that depends on how you load the data.
The code I provided looks for the last used row in column A, it might be
better to simplify your data model and limit column A as your search column
or use an end of data marker to signify the last row, the code could be
modifed to look for this marker rather than the used range.
If as you say the length of the search list can change how do you control
that other data in column A is not overwritten - or do you load this at the
same time? As I say the whole approach does very much depend on your load
routine.

Cheers
Nigel

"John" wrote in message
...
Thanks Nigel, I have several 'lists' in Sheet1 Column A that do not relate
to the search I am doing. In saying that I cannot specify a set range as
my
'source' range can change. Would I be better specifying a Range name for
my
'source' data


"Nigel" wrote in message
...
Hi John
This code will scan your entire list in column A on sheet1 and check if
the
value found is in the list in column A on sheet2
If the value on sheet1 is not found on sheet2 then the entire row on
sheet1
is deleted and all rows shifted up.
It adjusts for varying lengths of data in both sheet1 and sheet2.

Public Sub SelectiveDelete()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

Cheers
Nigel

"John" wrote in message
...
What code should I use if I want to delete entire Rows (and then move
all
other rows below up) if the value in say Sheet1 A1:A.... is not within
a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1 A1:A....
as
they will change in Row numbers daily - so I would want the code to
search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks

Nigel, can't get the code to work, it hits compile error at the line "Next
it" and states "Next without for" - any ideas?


"Nigel" wrote in message
...
Hi John
This code will scan your entire list in column A on sheet1 and check if
the
value found is in the list in column A on sheet2
If the value on sheet1 is not found on sheet2 then the entire row on
sheet1
is deleted and all rows shifted up.
It adjusts for varying lengths of data in both sheet1 and sheet2.

Public Sub SelectiveDelete()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

Cheers
Nigel

"John" wrote in message
...
What code should I use if I want to delete entire Rows (and then move
all
other rows below up) if the value in say Sheet1 A1:A.... is not within a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1 A1:A....
as
they will change in Row numbers daily - so I would want the code to
search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks

John,

The statement:

If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound =
False

needs to be all on one line, or else the compiler sees an If without a Then,
which causes the 'For without Next' message.

hth,

Doug Glancy

"John" wrote in message
...
Nigel, can't get the code to work, it hits compile error at the line "Next
it" and states "Next without for" - any ideas?


"Nigel" wrote in message
...
Hi John
This code will scan your entire list in column A on sheet1 and check if
the
value found is in the list in column A on sheet2
If the value on sheet1 is not found on sheet2 then the entire row on
sheet1
is deleted and all rows shifted up.
It adjusts for varying lengths of data in both sheet1 and sheet2.

Public Sub SelectiveDelete()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

Cheers
Nigel

"John" wrote in message
...
What code should I use if I want to delete entire Rows (and then move
all
other rows below up) if the value in say Sheet1 A1:A.... is not within
a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1 A1:A....
as
they will change in Row numbers daily - so I would want the code to
search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks

Thanks Doug, prob solved


"Doug Glancy" wrote in message
...
John,

The statement:

If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound
=
False

needs to be all on one line, or else the compiler sees an If without a
Then,
which causes the 'For without Next' message.

hth,

Doug Glancy

"John" wrote in message
...
Nigel, can't get the code to work, it hits compile error at the line
"Next
it" and states "Next without for" - any ideas?


"Nigel" wrote in message
...
Hi John
This code will scan your entire list in column A on sheet1 and check
if
the
value found is in the list in column A on sheet2
If the value on sheet1 is not found on sheet2 then the entire row on
sheet1
is deleted and all rows shifted up.
It adjusts for varying lengths of data in both sheet1 and sheet2.

Public Sub SelectiveDelete()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

Cheers
Nigel

"John" wrote in message
...
What code should I use if I want to delete entire Rows (and then
move
all
other rows below up) if the value in say Sheet1 A1:A.... is not
within
a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1
A1:A....
as
they will change in Row numbers daily - so I would want the code to
search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks

Guys, still have a problem, I tailored the code to what I thought would work
(see below). Simply I'm looking at all Rows in Sheet Sales Mix Column B for
those values in Sheet Master A451 that match Sheet Sales Mix "B...". If they
do match, then delete those rows in Sheet Sales Mix and move all other rows
up one etc.

I thought by modifying Nigels code it would work, it works great (and
correct) if my Sales Mix values were in Column A, I thought it was a simple
process of substituting B for A, but I stuck

Thanks




Public Sub SelectiveDelete()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sales Mix").Cells(Rows.Count, "B").End(xlUp).Row
LRowTable = Sheets("Master").Range("A451")

Sheets("Sales Mix").Activate
Dim ir As Long, it As Long, Found As Boolean
For ir = LRowTable To 1 Step -1
Found = False
For it = 1 To LRowData
If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then Found
= True
Next it
If Found Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True
End Sub


"Doug Glancy" wrote in message
...
John,

The statement:

If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound
=
False

needs to be all on one line, or else the compiler sees an If without a
Then,
which causes the 'For without Next' message.

hth,

Doug Glancy

"John" wrote in message
...
Nigel, can't get the code to work, it hits compile error at the line
"Next
it" and states "Next without for" - any ideas?


"Nigel" wrote in message
...
Hi John
This code will scan your entire list in column A on sheet1 and check
if
the
value found is in the list in column A on sheet2
If the value on sheet1 is not found on sheet2 then the entire row on
sheet1
is deleted and all rows shifted up.
It adjusts for varying lengths of data in both sheet1 and sheet2.

Public Sub SelectiveDelete()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

Cheers
Nigel

"John" wrote in message
...
What code should I use if I want to delete entire Rows (and then
move
all
other rows below up) if the value in say Sheet1 A1:A.... is not
within
a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1
A1:A....
as
they will change in Row numbers daily - so I would want the code to
search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks

John,

Don't know if you've solved this yet, but it looks like the line:

If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then Found

should be:

If Cells(ir, 2).Value = Sheets("Master").Cells(it, 2).Value Then Found

The Cells property takes the form Cells(row, column) so from what you said,
I think you want column 2.

hth,

Doug Glancy

"John" wrote in message
...
Guys, still have a problem, I tailored the code to what I thought would
work
(see below). Simply I'm looking at all Rows in Sheet Sales Mix Column B
for
those values in Sheet Master A451 that match Sheet Sales Mix "B...". If
they
do match, then delete those rows in Sheet Sales Mix and move all other
rows
up one etc.

I thought by modifying Nigels code it would work, it works great (and
correct) if my Sales Mix values were in Column A, I thought it was a
simple
process of substituting B for A, but I stuck

Thanks




Public Sub SelectiveDelete()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sales Mix").Cells(Rows.Count, "B").End(xlUp).Row
LRowTable = Sheets("Master").Range("A451")

Sheets("Sales Mix").Activate
Dim ir As Long, it As Long, Found As Boolean
For ir = LRowTable To 1 Step -1
Found = False
For it = 1 To LRowData
If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then
Found
= True
Next it
If Found Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True
End Sub


"Doug Glancy" wrote in message
...
John,

The statement:

If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then
NotFound
=
False

needs to be all on one line, or else the compiler sees an If without a
Then,
which causes the 'For without Next' message.

hth,

Doug Glancy

"John" wrote in message
...
Nigel, can't get the code to work, it hits compile error at the line
"Next
it" and states "Next without for" - any ideas?


"Nigel" wrote in message
...
Hi John
This code will scan your entire list in column A on sheet1 and check
if
the
value found is in the list in column A on sheet2
If the value on sheet1 is not found on sheet2 then the entire row on
sheet1
is deleted and all rows shifted up.
It adjusts for varying lengths of data in both sheet1 and sheet2.

Public Sub SelectiveDelete()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value
Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

Cheers
Nigel

"John" wrote in message
...
What code should I use if I want to delete entire Rows (and then
move
all
other rows below up) if the value in say Sheet1 A1:A.... is not
within
a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1
A1:A....
as
they will change in Row numbers daily - so I would want the code
to
search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks