ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Delete Rows if Certain Values are listed in a Range Name (https://www.excelbanter.com/excel-programming/302643-delete-rows-if-certain-values-listed-range-name.html)

John
Delete Rows if Certain Values are listed in a Range Name
 
I have the following code which endeavours to delete a Row if the value in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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

End Sub


The Range Name I am using the following dynamic formula to define the range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)

Ron de Bruin
Delete Rows if Certain Values are listed in a Range Name
 
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
I have the following code which endeavours to delete a Row if the value in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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

End Sub


The Range Name I am using the following dynamic formula to define the range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)

Ron de Bruin
Delete Rows if Certain Values are listed in a Range Name
 
Oops

Sheets("Sales Mix") I mean

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
I have the following code which endeavours to delete a Row if the value in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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

End Sub


The Range Name I am using the following dynamic formula to define the range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)

John
Delete Rows if Certain Values are listed in a Range Name
 
Ron, yeah the cell in C1 is the same as in A466. Your code originally worked
when I used specific references but when I changed to a dynamic range name
it just deleted my data


"Ron de Bruin" wrote in message
...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
I have the following code which endeavours to delete a Row if the value
in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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

End Sub


The Range Name I am using the following dynamic formula to define the
range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)

Ron de Bruin
Delete Rows if Certain Values are listed in a Range Name
 
Hi John

I have test it also with a dynamic range name.
No problem for me.

Can you send me a example workbook private.
Maybe i can find something

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
Ron, yeah the cell in C1 is the same as in A466. Your code originally worked
when I used specific references but when I changed to a dynamic range name
it just deleted my data


"Ron de Bruin" wrote in message
...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
I have the following code which endeavours to delete a Row if the value
in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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

End Sub


The Range Name I am using the following dynamic formula to define the
range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)

John
Delete Rows if Certain Values are listed in a Range Name
 
Ron

Just spotted where I was going wrong, I was calling it from another macro
and I had some wrong code in

Thanks again


"Ron de Bruin" wrote in message
...
Hi John

I have test it also with a dynamic range name.
No problem for me.

Can you send me a example workbook private.
Maybe i can find something

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
Ron, yeah the cell in C1 is the same as in A466. Your code originally
worked
when I used specific references but when I changed to a dynamic range
name
it just deleted my data


"Ron de Bruin" wrote in message
...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
I have the following code which endeavours to delete a Row if the
value
in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and
instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in
the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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

End Sub


The Range Name I am using the following dynamic formula to define
the
range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)

John
Delete Rows if Certain Values are listed in a Range Name
 
Ron

I thought I had it but it is still deleting my data. I copied the code to a
new workbook with some limited data and it works but with my live data it
just deletes it, I didn't delete or change any part of the code so I am
baffled what is going wrong. There is little point in taking you up on your
kind offer as it works




"Ron de Bruin" wrote in message
...
Hi John

I have test it also with a dynamic range name.
No problem for me.

Can you send me a example workbook private.
Maybe i can find something

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
Ron, yeah the cell in C1 is the same as in A466. Your code originally
worked
when I used specific references but when I changed to a dynamic range
name
it just deleted my data


"Ron de Bruin" wrote in message
...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
I have the following code which endeavours to delete a Row if the
value
in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and
instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in
the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

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

End Sub


The Range Name I am using the following dynamic formula to define
the
range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)


All times are GMT +1. The time now is 01:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com