Delete code

C

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
 
S

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
 
C

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)
 
D

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
 
C

chrisnsmith

That worked great, thank you

Dave Peterson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top