PC Review


Reply
Thread Tools Rate Thread

Change DeleteValue1 to named range

 
 
J.W. Aldridge
Guest
Posts: n/a
 
      19th Oct 2010
Need to change delete criteria to all values in a named range.
Named range = "apples".

Sub Delete_with_Autofilter_Two_Criteria1()
Dim DeleteValue1 As String
Dim DeleteValue2 As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the two values that you want to delete
DeleteValue1 = "completed"
DeleteValue2 = "discards"

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("I1:I" & .Rows.Count).AutoFilter Field:=1, _
Criteria1:=DeleteValue1, Operator:=xlOr,
Criteria2:=DeleteValue2

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
change named range geebee Microsoft Excel Programming 5 14th Aug 2008 06:30 PM
Change Event on a named range GregR Microsoft Excel Programming 2 12th Jul 2005 09:37 PM
How to unname or change a named range =?Utf-8?B?ZHJmcnll?= Microsoft Excel Misc 2 8th Nov 2004 01:05 AM
Change named range value =?Utf-8?B?Sm9lIEJvdWNoZXI=?= Microsoft Excel Programming 2 30th Sep 2004 01:41 AM
change named range within macro JulieM Microsoft Excel Programming 5 19th Mar 2004 08:17 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:16 PM.