Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all rows but rows with value =
hi,
i'm trying to modify the following code from Ron de Bruns Excel page. i need the macro to delete all rows that do not equal "abc" in column C; instead of deleting all rows with "abc" as it currently does. how do i modify this? If i use "<" by Criteria1 i get an error. also, would it be possible to create a prompt for this 'do not delete' value? Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "abc" With ActiveSheet .Range("A1:C40000").AutoFilter Field:=3, Criteria1:=DeleteValue With ActiveSheet.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 .AutoFilterMode = False End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all rows but rows with value =
Sub Delete_with_Autofilter()
Dim DeleteValue As String Dim rng As Range DeleteValue = InputBox("Please Enter Data to Filter") With ActiveSheet .Range("A1:C40000").AutoFilter Field:=3, Criteria1:="<" & DeleteValue With ActiveSheet.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 .AutoFilterMode = False End With End Sub Give that a shot and let me know if it works. Charles Chickering yoram wrote: hi, i'm trying to modify the following code from Ron de Bruns Excel page. i need the macro to delete all rows that do not equal "abc" in column C; instead of deleting all rows with "abc" as it currently does. how do i modify this? If i use "<" by Criteria1 i get an error. also, would it be possible to create a prompt for this 'do not delete' value? Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "abc" With ActiveSheet .Range("A1:C40000").AutoFilter Field:=3, Criteria1:=DeleteValue With ActiveSheet.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 .AutoFilterMode = False End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all rows but rows with value =
That works great. Thanks a bunch!!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Delete rows with numeric values, leave rows with text | Excel Programming | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) |