Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete code
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete code
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 ------------- If this helps, please click the Yes button. Cheers, Shane Devenshire "chrisnsmith" wrote: 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.Delete Shift:=xlUp End With 'Remove the AutoFilter .AutoFilterMode = False End With With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete code
That doesn't work. It still deletes the entire row.. I only want to delete
the info in Row(Column A thru Column E) "Shane Devenshire" wrote: 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 ------------- If this helps, please click the Yes button. Cheers, Shane Devenshire "chrisnsmith" wrote: 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.Delete Shift:=xlUp End With 'Remove the AutoFilter .AutoFilterMode = False End With With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete code
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 wrote: That doesn't work. It still deletes the entire row.. I only want to delete the info in Row(Column A thru Column E) "Shane Devenshire" wrote: 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 ------------- If this helps, please click the Yes button. Cheers, Shane Devenshire "chrisnsmith" wrote: 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.Delete Shift:=xlUp End With 'Remove the AutoFilter .AutoFilterMode = False End With With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete code
That worked great, thank you
"Dave Peterson" wrote: 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 wrote: That doesn't work. It still deletes the entire row.. I only want to delete the info in Row(Column A thru Column E) "Shane Devenshire" wrote: 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 ------------- If this helps, please click the Yes button. Cheers, Shane Devenshire "chrisnsmith" wrote: 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.Delete Shift:=xlUp End With 'Remove the AutoFilter .AutoFilterMode = False End With With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Every Second Duplicate Code | Excel Discussion (Misc queries) | |||
delete code keeps going and coing | Excel Discussion (Misc queries) | |||
Code to delete an Excel File | Excel Discussion (Misc queries) | |||
VBA code to delete rows | Excel Discussion (Misc queries) | |||
code to delete rows | Excel Discussion (Misc queries) |