J
John
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)
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)