Delete row
 

The following code was provided to me by Chip Pearson. As it is now it will
delete all rows that contain an R in the account column. What I would like
to do now is edit the code to delete rows which contain an R in certain
places. The following example should illustrate what I need to do.

Acct Month Long Short
R3423
R7834
243 R0023
432 R3404
QR459
QR232
If the R appears in the fourth place I need to delete that row otherwise do
nothing.


Sub SortandClear()
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 Sheets("Seg Futures")

'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

Delete row
 

"chrisnsmith" wrote:

The following code was provided to me by Ron de Bruin. As it is now it will
delete all rows that contain an R in the account column. What I would like
to do now is edit the code to delete rows which contain an R in certain
places. The following example should illustrate what I need to do.

Acct Month Long Short
R3423
R7834
243 R0023
432 R3404
QR459
QR232
If the R appears in the fourth place I need to delete that row otherwise do
nothing.


Sub SortandClear()
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 Sheets("Seg Futures")

'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

Delete row
 

"chrisnsmith" wrote:

The following code was provided to me by Ron de Bruin. As it is now it will
delete all rows that contain an R in the account column. What I would like
to do now is edit the code to delete rows which contain an R in certain
places. The following example should illustrate what I need to do.

Acct Month Long Short
R3423
R7834
243 R0023
432 R3404
QR459
QR232
If the R appears in the fourth place I need to delete that row otherwise do
nothing.


Sub SortandClear()
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 Sheets("Seg Futures")

'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

Delete row
 

Need to give credit where credit is due. This code was provided by Ron de
Bruin not Chip Pearson.

"chrisnsmith" wrote:

The following code was provided to me by Chip Pearson. As it is now it will
delete all rows that contain an R in the account column. What I would like
to do now is edit the code to delete rows which contain an R in certain
places. The following example should illustrate what I need to do.

Acct Month Long Short
R3423
R7834
243 R0023
432 R3404
QR459
QR232
If the R appears in the fourth place I need to delete that row otherwise do
nothing.


Sub SortandClear()
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 Sheets("Seg Futures")

'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