Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter code to delete rows
I want to use Ron de Bruin's
http://www.rondebruin.nl/delete.htm AutoFilter code to delete a lot of rows. The criteria for rows to be deleted is: =OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9)) ;LEFT(D9;4)="Side") from row 9 to row 30.000. Any help on how to put this formula into Ron's below code: Regards Hans Knudsen Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range Dim calcmode As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Fill in the value that you want to delete 'Tip: use DeleteValue = "<ron" to delete rows without ron DeleteValue = "ron" '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:=DeleteValue 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.programming
|
|||
|
|||
AutoFilter code to delete rows
Hans,
I think your goung to have be a bit more definitive about what the delete criteria is. The worksheet formula you gave =OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9)) ;LEFT(D9;4)="Side") evaluates like this which wouldn't leave may lines un-deleted, All 3 cells Blank= True Number in B9 text in C9= True Number in B9, "Side" in D9=True Number in B9 text in C9 text in D9= True B9 blank, text in c9, text in D9= True B9 & C9 blank text in d9= True B9 blank , text in C9, d9 blank= True Mike "Hans Knudsen" wrote: I want to use Ron de Bruin's http://www.rondebruin.nl/delete.htm AutoFilter code to delete a lot of rows. The criteria for rows to be deleted is: =OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9)) ;LEFT(D9;4)="Side") from row 9 to row 30.000. Any help on how to put this formula into Ron's below code: Regards Hans Knudsen Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range Dim calcmode As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Fill in the value that you want to delete 'Tip: use DeleteValue = "<ron" to delete rows without ron DeleteValue = "ron" '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:=DeleteValue 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter code to delete rows
Hi,
You are looking for all cells that are not number or they are numbers and they are text. Can't be both numbers and text. Forget the formula and tell us in words what you want to make for deletion. -- Thanks, Shane Devenshire "Hans Knudsen" wrote: I want to use Ron de Bruin's http://www.rondebruin.nl/delete.htm AutoFilter code to delete a lot of rows. The criteria for rows to be deleted is: =OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9)) ;LEFT(D9;4)="Side") from row 9 to row 30.000. Any help on how to put this formula into Ron's below code: Regards Hans Knudsen Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range Dim calcmode As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Fill in the value that you want to delete 'Tip: use DeleteValue = "<ron" to delete rows without ron DeleteValue = "ron" '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:=DeleteValue 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter code to delete rows
Mike
This extract I have is really the most awful thing I have ever seen. Will try to explain in some more detail: There are a lot of blank rows which I need to delete. I meant to find such rows by: NOT(ISNUMBER(Bx)) Most of the rows I need to keep have a date in column B (dd-mm-yyyy), that is a 5-digit number and also have numbers in column C and D. Some rows however have a 4-digit number in column B and in the same row there is a text in column C (and number in column D). I meant to find such rows by : AND(ISNUMBER(Bx),ISTEXT(C)). Finally there are rows with a date in column B (dd-mm-yyyy) and in the same row there is (a time in column C) and a text in column D where the first 4 characters are "Side". I meant to find that by: LEFT(Dx;4)="Side" I know the above is just an explanation of the formula and actually I feel that this is not what you ask for. For blank cells it is sufficient to check column B I would gladly tell more if I just knew exactly what more information you need. Hans "Mike H" wrote in message ... Hans, I think your goung to have be a bit more definitive about what the delete criteria is. The worksheet formula you gave =OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9)) ;LEFT(D9;4)="Side") evaluates like this which wouldn't leave may lines un-deleted, All 3 cells Blank= True Number in B9 text in C9= True Number in B9, "Side" in D9=True Number in B9 text in C9 text in D9= True B9 blank, text in c9, text in D9= True B9 & C9 blank text in d9= True B9 blank , text in C9, d9 blank= True Mike "Hans Knudsen" wrote: I want to use Ron de Bruin's http://www.rondebruin.nl/delete.htm AutoFilter code to delete a lot of rows. The criteria for rows to be deleted is: =OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9)) ;LEFT(D9;4)="Side") from row 9 to row 30.000. Any help on how to put this formula into Ron's below code: Regards Hans Knudsen Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range Dim calcmode As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Fill in the value that you want to delete 'Tip: use DeleteValue = "<ron" to delete rows without ron DeleteValue = "ron" '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:=DeleteValue 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter code to delete rows
I have tried to tell in words what I want to delete in my reply to Mike H.
Yuo wrote: "You are looking for all cells that are not number or they are numbers and they are text. Can't be both numbers and text" I am (among other things) loking for cells that have a number in column B and text in column C, not for cells that at the same time have both a number and a text in the same cell as you seem to assume. Hans Knudsen "ShaneDevenshire" wrote in message ... Hi, You are looking for all cells that are not number or they are numbers and they are text. Can't be both numbers and text. Forget the formula and tell us in words what you want to make for deletion. -- Thanks, Shane Devenshire "Hans Knudsen" wrote: I want to use Ron de Bruin's http://www.rondebruin.nl/delete.htm AutoFilter code to delete a lot of rows. The criteria for rows to be deleted is: =OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9)) ;LEFT(D9;4)="Side") from row 9 to row 30.000. Any help on how to put this formula into Ron's below code: Regards Hans Knudsen Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range Dim calcmode As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Fill in the value that you want to delete 'Tip: use DeleteValue = "<ron" to delete rows without ron DeleteValue = "ron" '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:=DeleteValue 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows with Autofilter and partial cell. | Excel Programming | |||
VBA code to delete rows | Excel Discussion (Misc queries) | |||
How to use autofilter to delete duplicate rows (2nd criteria) ? | Excel Programming | |||
code to delete rows | Excel Discussion (Misc queries) | |||
delete rows autofilter | Excel Programming |