Delete code

[chrisnsmith]

Using this code form Ron de Bruin. But need to know how to set the range to
delete as rows in Columns A thru E only.

Sub Delete_with_Autofilter_Two_Criteria()
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 = "*R*"
DeleteValue2 = "5032"

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

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("A1:A" & .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

 

[Shane Devenshire]

Hi,

Try this:

If Not rng Is Nothing Then rng.Delete Shift:=xlUp

to replace the line that reads

If Not rng Is Nothing Then rng.entirerow.Delete

 

[chrisnsmith]

That doesn't work. It still deletes the entire row.. I only want to delete
the info in Row(Column A thru Column E)

 

[Dave Peterson]

If you've ever tried to manually delete the visible rows in the autofilter range
(and leave the remaining columns untouched), you'll see that it can't be done.

So one way is to remove that filter (after you determined the visible cells) and
then do the delete.

Option Explicit
Sub Delete_with_Autofilter_Two_Criteria()
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 = "*R*"
DeleteValue2 = "5032"

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

'Firstly, remove the AutoFilter
.AutoFilterMode = False

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

'resized to be 5 columns!!!
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 5) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

'Remove the AutoFilter
.AutoFilterMode = False
rng.Delete shift:=xlShiftUp

End With

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

End Sub

 

[chrisnsmith]

That worked great, thank you

If you've ever tried to manually delete the visible rows in the autofilter range
(and leave the remaining columns untouched), you'll see that it can't be done.

So one way is to remove that filter (after you determined the visible cells) and
then do the delete.

Option Explicit
Sub Delete_with_Autofilter_Two_Criteria()
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 = "*R*"
DeleteValue2 = "5032"

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

'Firstly, remove the AutoFilter
.AutoFilterMode = False

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

'resized to be 5 columns!!!
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 5) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

'Remove the AutoFilter
.AutoFilterMode = False
rng.Delete shift:=xlShiftUp

End With

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

End Sub