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