Delete a Row if a Certain Value is in Column C
 

I am looking for some code that will delete an entire row in Sheet "Sales
Mix" if Column C contains certain values. These values are held within a
Range Name "Products_Not_Required". This range covers A:B and the values are
in Column A.

At the moment I have the following code which I can only get to work by
specifying one value within the code itself, that value is 7 i.e. if value
37 is in Column C anywhere then the entire row is deleted and all Rows shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
..Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

Hi John

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message ...
I am looking for some code that will delete an entire row in Sheet "Sales
Mix" if Column C contains certain values. These values are held within a
Range Name "Products_Not_Required". This range covers A:B and the values are
in Column A.

At the moment I have the following code which I can only get to work by
specifying one value within the code itself, that value is 7 i.e. if value
37 is in Column C anywhere then the entire row is deleted and all Rows shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

Hi Ron, that example I had was actually your code. Would prefer to use a
Range name as then I don't have to worry about changing the VB code as other
users will be updating the "Products_Not_required"


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

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message
...
I am looking for some code that will delete an entire row in Sheet
"Sales
Mix" if Column C contains certain values. These values are held within a
Range Name "Products_Not_Required". This range covers A:B and the values
are
in Column A.

At the moment I have the following code which I can only get to work by
specifying one value within the code itself, that value is 7 i.e. if
value
37 is in Column C anywhere then the entire row is deleted and all Rows
shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

Hi

In which row your data start?
The "Products_Not_required' range must be above that line.



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


"John" wrote in message ...
Hi Ron, that example I had was actually your code. Would prefer to use a
Range name as then I don't have to worry about changing the VB code as other
users will be updating the "Products_Not_required"


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

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message
...
I am looking for some code that will delete an entire row in Sheet
"Sales
Mix" if Column C contains certain values. These values are held within a
Range Name "Products_Not_Required". This range covers A:B and the values
are
in Column A.

At the moment I have the following code which I can only get to work by
specifying one value within the code itself, that value is 7 i.e. if
value
37 is in Column C anywhere then the entire row is deleted and all Rows
shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

Ron

My data i.e. the rows of data I may want to delete starts in C2 on Sheet
Sales Mix, the "Products_Not_Required" named range is on Sheet Mster
starting at A468. IS it not possible to specify a named range within a
Macro?

Thanks


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

In which row your data start?
The "Products_Not_required' range must be above that line.



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


"John" wrote in message
...
Hi Ron, that example I had was actually your code. Would prefer to use a
Range name as then I don't have to worry about changing the VB code as
other
users will be updating the "Products_Not_required"


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

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message
...
I am looking for some code that will delete an entire row in Sheet
"Sales
Mix" if Column C contains certain values. These values are held
within a
Range Name "Products_Not_Required". This range covers A:B and the
values
are
in Column A.

At the moment I have the following code which I can only get to work
by
specifying one value within the code itself, that value is 7 i.e. if
value
37 is in Column C anywhere then the entire row is deleted and all
Rows
shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

range("Products_Not_Required")

would be how you refer to a named range.

msgbox range("Products_Not_Required").Address(External:=T rue)

or

for each cell in range("Products_Not_Required")

as examples.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Ron

My data i.e. the rows of data I may want to delete starts in C2 on Sheet
Sales Mix, the "Products_Not_Required" named range is on Sheet Mster
starting at A468. IS it not possible to specify a named range within a
Macro?

Thanks


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

In which row your data start?
The "Products_Not_required' range must be above that line.



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


"John" wrote in message
...
Hi Ron, that example I had was actually your code. Would prefer to use
a
Range name as then I don't have to worry about changing the VB code as
other
users will be updating the "Products_Not_required"


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

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message
...
I am looking for some code that will delete an entire row in Sheet
"Sales
Mix" if Column C contains certain values. These values are held
within a
Range Name "Products_Not_Required". This range covers A:B and the
values
are
in Column A.

At the moment I have the following code which I can only get to
work
by
specifying one value within the code itself, that value is 7 i.e.
if
value
37 is in Column C anywhere then the entire row is deleted and all
Rows
shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

Hi John

Sorry for the late response

Maybe this is a faster solution for you.
I use AdvancedFilter in this example

Try it in a test workbook

The Sheet with the criteria is named "CriteriaSheet" in this example
A1 must have the same header as C1 in the activesheet with data.
In A2 :A? you fill in the values you want to delete.

C1:C? is the data range on the Activesheet
A1:A? is the criteria on the sheet "CriteriaSheet"

Note: A1 and C1 must have the same header


Sub Filtertest()
Dim rng As Range
Dim CriteriaRng As Range

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("CriteriaSheet")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the ActiveSheet
Set CriteriaRng = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
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

End Sub





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


"John" wrote in message ...
Ron

My data i.e. the rows of data I may want to delete starts in C2 on Sheet
Sales Mix, the "Products_Not_Required" named range is on Sheet Mster
starting at A468. IS it not possible to specify a named range within a
Macro?

Thanks


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

In which row your data start?
The "Products_Not_required' range must be above that line.



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


"John" wrote in message
...
Hi Ron, that example I had was actually your code. Would prefer to use a
Range name as then I don't have to worry about changing the VB code as
other
users will be updating the "Products_Not_required"


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

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message
...
I am looking for some code that will delete an entire row in Sheet
"Sales
Mix" if Column C contains certain values. These values are held
within a
Range Name "Products_Not_Required". This range covers A:B and the
values
are
in Column A.

At the moment I have the following code which I can only get to work
by
specifying one value within the code itself, that value is 7 i.e. if
value
37 is in Column C anywhere then the entire row is deleted and all
Rows
shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

Many thanks Ron for taking the time to reply, you have helped me with a few
items on the message board. Your code works very well, and its very quick

As I may have items below the 'Critera range' which are unrelated to the
data range is there any way within your line Set CriteriaRng = .Range("A1:A"
& .Range("A" & Rows.Count).End(xlUp).Row) where I can code in a Range Name?

Thanks again



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

Sorry for the late response

Maybe this is a faster solution for you.
I use AdvancedFilter in this example

Try it in a test workbook

The Sheet with the criteria is named "CriteriaSheet" in this example
A1 must have the same header as C1 in the activesheet with data.
In A2 :A? you fill in the values you want to delete.

C1:C? is the data range on the Activesheet
A1:A? is the criteria on the sheet "CriteriaSheet"

Note: A1 and C1 must have the same header


Sub Filtertest()
Dim rng As Range
Dim CriteriaRng As Range

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("CriteriaSheet")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = .Range("A1:A" & .Range("A" &
Rows.Count).End(xlUp).Row)
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

End Sub





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


"John" wrote in message
...
Ron

My data i.e. the rows of data I may want to delete starts in C2 on Sheet
Sales Mix, the "Products_Not_Required" named range is on Sheet Mster
starting at A468. IS it not possible to specify a named range within a
Macro?

Thanks


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

In which row your data start?
The "Products_Not_required' range must be above that line.



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


"John" wrote in message
...
Hi Ron, that example I had was actually your code. Would prefer to
use a
Range name as then I don't have to worry about changing the VB code
as
other
users will be updating the "Products_Not_required"


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

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message
...
I am looking for some code that will delete an entire row in
Sheet
"Sales
Mix" if Column C contains certain values. These values are held
within a
Range Name "Products_Not_Required". This range covers A:B and
the
values
are
in Column A.

At the moment I have the following code which I can only get to
work
by
specifying one value within the code itself, that value is 7
i.e. if
value
37 is in Column C anywhere then the entire row is deleted and
all
Rows
shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

Hi John

& .Range("A" & Rows.Count).End(xlUp).Row)

One way is to change "Rows.Count" to a row number
(the row above the row where your other data start)

I like that because it will expand the range if you add a item.

I will try it this evening or tomorrow with a range name John.
I let you know.

Maybe the others have Ideas to


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


"John" wrote in message ...
Many thanks Ron for taking the time to reply, you have helped me with a few
items on the message board. Your code works very well, and its very quick

As I may have items below the 'Critera range' which are unrelated to the
data range is there any way within your line Set CriteriaRng = .Range("A1:A"
& .Range("A" & Rows.Count).End(xlUp).Row) where I can code in a Range Name?

Thanks again



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

Sorry for the late response

Maybe this is a faster solution for you.
I use AdvancedFilter in this example

Try it in a test workbook

The Sheet with the criteria is named "CriteriaSheet" in this example
A1 must have the same header as C1 in the activesheet with data.
In A2 :A? you fill in the values you want to delete.

C1:C? is the data range on the Activesheet
A1:A? is the criteria on the sheet "CriteriaSheet"

Note: A1 and C1 must have the same header


Sub Filtertest()
Dim rng As Range
Dim CriteriaRng As Range

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("CriteriaSheet")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = .Range("A1:A" & .Range("A" &
Rows.Count).End(xlUp).Row)
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

End Sub





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


"John" wrote in message
...
Ron

My data i.e. the rows of data I may want to delete starts in C2 on Sheet
Sales Mix, the "Products_Not_Required" named range is on Sheet Mster
starting at A468. IS it not possible to specify a named range within a
Macro?

Thanks


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

In which row your data start?
The "Products_Not_required' range must be above that line.



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


"John" wrote in message
...
Hi Ron, that example I had was actually your code. Would prefer to
use a
Range name as then I don't have to worry about changing the VB code
as
other
users will be updating the "Products_Not_required"


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

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message
...
I am looking for some code that will delete an entire row in
Sheet
"Sales
Mix" if Column C contains certain values. These values are held
within a
Range Name "Products_Not_Required". This range covers A:B and
the
values
are
in Column A.

At the moment I have the following code which I can only get to
work
by
specifying one value within the code itself, that value is 7
i.e. if
value
37 is in Column C anywhere then the entire row is deleted and
all
Rows
shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

Hi John

No problem wen you use this
Set CriteriaRng = Range("rangename")

See Debra's site for how to use a dynamic name
http://www.contextures.com/xlNames01.html#Dynamic



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


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

& .Range("A" & Rows.Count).End(xlUp).Row)

One way is to change "Rows.Count" to a row number
(the row above the row where your other data start)

I like that because it will expand the range if you add a item.

I will try it this evening or tomorrow with a range name John.
I let you know.

Maybe the others have Ideas to


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


"John" wrote in message ...
Many thanks Ron for taking the time to reply, you have helped me with a few
items on the message board. Your code works very well, and its very quick

As I may have items below the 'Critera range' which are unrelated to the
data range is there any way within your line Set CriteriaRng = .Range("A1:A"
& .Range("A" & Rows.Count).End(xlUp).Row) where I can code in a Range Name?

Thanks again



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

Sorry for the late response

Maybe this is a faster solution for you.
I use AdvancedFilter in this example

Try it in a test workbook

The Sheet with the criteria is named "CriteriaSheet" in this example
A1 must have the same header as C1 in the activesheet with data.
In A2 :A? you fill in the values you want to delete.

C1:C? is the data range on the Activesheet
A1:A? is the criteria on the sheet "CriteriaSheet"

Note: A1 and C1 must have the same header


Sub Filtertest()
Dim rng As Range
Dim CriteriaRng As Range

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("CriteriaSheet")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = .Range("A1:A" & .Range("A" &
Rows.Count).End(xlUp).Row)
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

End Sub





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


"John" wrote in message
...
Ron

My data i.e. the rows of data I may want to delete starts in C2 on Sheet
Sales Mix, the "Products_Not_Required" named range is on Sheet Mster
starting at A468. IS it not possible to specify a named range within a
Macro?

Thanks


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

In which row your data start?
The "Products_Not_required' range must be above that line.



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


"John" wrote in message
...
Hi Ron, that example I had was actually your code. Would prefer to
use a
Range name as then I don't have to worry about changing the VB code
as
other
users will be updating the "Products_Not_required"


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

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message
...
I am looking for some code that will delete an entire row in
Sheet
"Sales
Mix" if Column C contains certain values. These values are held
within a
Range Name "Products_Not_Required". This range covers A:B and
the
values
are
in Column A.

At the moment I have the following code which I can only get to
work
by
specifying one value within the code itself, that value is 7
i.e. if
value
37 is in Column C anywhere then the entire row is deleted and
all
Rows
shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub

Delete a Row if a Certain Value is in Column C
 

Thanks Ron


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

No problem wen you use this
Set CriteriaRng = Range("rangename")

See Debra's site for how to use a dynamic name
http://www.contextures.com/xlNames01.html#Dynamic



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


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

& .Range("A" & Rows.Count).End(xlUp).Row)

One way is to change "Rows.Count" to a row number
(the row above the row where your other data start)

I like that because it will expand the range if you add a item.

I will try it this evening or tomorrow with a range name John.
I let you know.

Maybe the others have Ideas to


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


"John" wrote in message
...
Many thanks Ron for taking the time to reply, you have helped me with
a few
items on the message board. Your code works very well, and its very
quick

As I may have items below the 'Critera range' which are unrelated to
the
data range is there any way within your line Set CriteriaRng =
..Range("A1:A"
& .Range("A" & Rows.Count).End(xlUp).Row) where I can code in a Range
Name?

Thanks again



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

Sorry for the late response

Maybe this is a faster solution for you.
I use AdvancedFilter in this example

Try it in a test workbook

The Sheet with the criteria is named "CriteriaSheet" in this example
A1 must have the same header as C1 in the activesheet with data.
In A2 :A? you fill in the values you want to delete.

C1:C? is the data range on the Activesheet
A1:A? is the criteria on the sheet "CriteriaSheet"

Note: A1 and C1 must have the same header


Sub Filtertest()
Dim rng As Range
Dim CriteriaRng As Range

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("CriteriaSheet")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in
the
ActiveSheet
Set CriteriaRng = .Range("A1:A" & .Range("A" &
Rows.Count).End(xlUp).Row)
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

End Sub





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


"John" wrote in message
...
Ron

My data i.e. the rows of data I may want to delete starts in C2 on
Sheet
Sales Mix, the "Products_Not_Required" named range is on Sheet
Mster
starting at A468. IS it not possible to specify a named range
within a
Macro?

Thanks


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

In which row your data start?
The "Products_Not_required' range must be above that line.



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


"John" wrote in message
...
Hi Ron, that example I had was actually your code. Would
prefer to
use a
Range name as then I don't have to worry about changing the VB
code
as
other
users will be updating the "Products_Not_required"


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

I have a example that use a Inputbox
Maybe you like that.
http://www.rondebruin.nl/delete.htm
(See the Find examples)

Post back if you really want to use the data in Column A
I will make a example for you then

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


"John" wrote in message
...
I am looking for some code that will delete an entire row
in
Sheet
"Sales
Mix" if Column C contains certain values. These values are
held
within a
Range Name "Products_Not_Required". This range covers A:B
and
the
values
are
in Column A.

At the moment I have the following code which I can only
get to
work
by
specifying one value within the code itself, that value is
7
i.e. if
value
37 is in Column C anywhere then the entire row is deleted
and
all
Rows
shift
up one.

Thanks

Public Sub SelectiveDelete()
Application.ScreenUpdating = False

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

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long


Sheets("Sales Mix").Select
With ActiveSheet
.DisplayPageBreaks = False

StartRow = 2
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then

ElseIf .Cells(Lrow, "c").Value = "37" Then
.Rows(Lrow).EntireRow.Delete Shift:=xlUp

End If
Next
End With


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

End Sub