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