| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows in a range using Autofilter
I am using the following code to delete rows in a range.
Private Sub Check_For_Existing_Oracle_No_Click() Dim rng As Range Dim sh As Worksheet Set sh = Worksheets("Compiled Totals") Set rng = sh.Range(sh.Cells(9, "C"), _ sh.Cells(sh.Rows.Count, "C").End(xlUp)) rng.AutoFilter rng.AutoFilter Field:=1, Criteria1:=Range("oracle_no").Value Set rng = Sheets("Compiled Totals").Range(rng.Address).SpecialCells(xlCellTyp eVisible) rng.EntireRow.Delete Sheets("Compiled Totals").Range("C9").AutoFilter End Sub My data is as follows in column C, and the oracle_no I'm testing is 23356. The code above works, however, I not only delete all the rows in which oracle_no = 23356, but I also delete the row right above the first 23356 (where oracle_no = 23709). Is there another way to do this? The autofilter function seems a little quirky, and I want to make sure I delete the correct rows. Also, if I wanted to count the number of rows for which oracle_no = 23356, how would I do that? I want to display that value in a message prompt to the user, letting them know that they are requesting to delete x number of records. Thanks! Connie "Oracle ID #" 23709 23709 23709 23709 23709 23709 23709 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 29697 29697 24898 24898 24898 24898 24898 24898 29697 29697 29697 29697 29697 29697 29697 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting rows in a range using Autofilter
Hi there Connie,
I made a few adjustments to your code. You might want to try something like this... Private Sub Check_For_Existing_Oracle_No_Click() Dim rng As Range, rngDel As Range Dim sh As Worksheet, n As Long On Error GoTo ErrHandle Call ToggleEvents(False) Set sh = Worksheets("Compiled Totals") Set rng = sh.Range(sh.Cells(9, "C"), sh.Cells(sh.Rows.Count, "C").End(xlUp)) Set rngDel = sh.Range(sh.Cells(10, "C"), sh.Cells(sh.Rows.Count, "C").End(xlUp)) sh.AutoFilterMode = False rng.AutoFilter Field:=1, Criteria1:=sh.Range("oracle_no").Value, Operator:=xlOr, Criteria2:="23356" n = rngDel.SpecialCells(xlCellTypeVisible).Rows.Count If n = 0 Then MsgBox "No records matched your criteria!", vbInformation, "NO RECORDS" GoTo ErrHandle Else If MsgBox("Do you wish to delete " & n & " records?", vbYesNo, "DELETE " & n & " RECORDS?") < vbYes Then GoTo ErrHandle End If End If rngDel.SpecialCells(xlCellTypeVisible).EntireRow.D elete ErrHandle: sh.AutoFilterMode = False Call ToggleEvents(True) End Sub Sub ToggleEvents(blnState As Boolean) With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState = True Then .CutCopyMode = False .StatusBar = False End If End With End Sub Let us know how it works for you. HTH -- Regards, Zack Barresse, aka firefytr "Connie" wrote in message ups.com... I am using the following code to delete rows in a range. Private Sub Check_For_Existing_Oracle_No_Click() Dim rng As Range Dim sh As Worksheet Set sh = Worksheets("Compiled Totals") Set rng = sh.Range(sh.Cells(9, "C"), _ sh.Cells(sh.Rows.Count, "C").End(xlUp)) rng.AutoFilter rng.AutoFilter Field:=1, Criteria1:=Range("oracle_no").Value Set rng = Sheets("Compiled Totals").Range(rng.Address).SpecialCells(xlCellTyp eVisible) rng.EntireRow.Delete Sheets("Compiled Totals").Range("C9").AutoFilter End Sub My data is as follows in column C, and the oracle_no I'm testing is 23356. The code above works, however, I not only delete all the rows in which oracle_no = 23356, but I also delete the row right above the first 23356 (where oracle_no = 23709). Is there another way to do this? The autofilter function seems a little quirky, and I want to make sure I delete the correct rows. Also, if I wanted to count the number of rows for which oracle_no = 23356, how would I do that? I want to display that value in a message prompt to the user, letting them know that they are requesting to delete x number of records. Thanks! Connie "Oracle ID #" 23709 23709 23709 23709 23709 23709 23709 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 29697 29697 24898 24898 24898 24898 24898 24898 29697 29697 29697 29697 29697 29697 29697 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting rows in a range using Autofilter
Hi there Connie,
I made a few adjustments to your code. You might want to try something like this... Private Sub Check_For_Existing_Oracle_No_Click() Dim rng As Range, rngDel As Range Dim sh As Worksheet, n As Long On Error GoTo ErrHandle Call ToggleEvents(False) Set sh = Worksheets("Compiled Totals") Set rng = sh.Range(sh.Cells(9, "C"), sh.Cells(sh.Rows.Count, "C").End(xlUp)) Set rngDel = sh.Range(sh.Cells(10, "C"), sh.Cells(sh.Rows.Count, "C").End(xlUp)) sh.AutoFilterMode = False rng.AutoFilter Field:=1, Criteria1:=sh.Range("oracle_no").Value, Operator:=xlOr, Criteria2:="23356" n = rngDel.SpecialCells(xlCellTypeVisible).Rows.Count If n = 0 Then MsgBox "No records matched your criteria!", vbInformation, "NO RECORDS" GoTo ErrHandle Else If MsgBox("Do you wish to delete " & n & " records?", vbYesNo, "DELETE " & n & " RECORDS?") < vbYes Then GoTo ErrHandle End If End If rngDel.SpecialCells(xlCellTypeVisible).EntireRow.D elete ErrHandle: sh.AutoFilterMode = False Call ToggleEvents(True) End Sub Sub ToggleEvents(blnState As Boolean) With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState = True Then .CutCopyMode = False .StatusBar = False End If End With End Sub Let us know how it works for you. HTH -- Regards, Zack Barresse, aka firefytr "Connie" wrote in message ups.com... I am using the following code to delete rows in a range. Private Sub Check_For_Existing_Oracle_No_Click() Dim rng As Range Dim sh As Worksheet Set sh = Worksheets("Compiled Totals") Set rng = sh.Range(sh.Cells(9, "C"), _ sh.Cells(sh.Rows.Count, "C").End(xlUp)) rng.AutoFilter rng.AutoFilter Field:=1, Criteria1:=Range("oracle_no").Value Set rng = Sheets("Compiled Totals").Range(rng.Address).SpecialCells(xlCellTyp eVisible) rng.EntireRow.Delete Sheets("Compiled Totals").Range("C9").AutoFilter End Sub My data is as follows in column C, and the oracle_no I'm testing is 23356. The code above works, however, I not only delete all the rows in which oracle_no = 23356, but I also delete the row right above the first 23356 (where oracle_no = 23709). Is there another way to do this? The autofilter function seems a little quirky, and I want to make sure I delete the correct rows. Also, if I wanted to count the number of rows for which oracle_no = 23356, how would I do that? I want to display that value in a message prompt to the user, letting them know that they are requesting to delete x number of records. Thanks! Connie "Oracle ID #" 23709 23709 23709 23709 23709 23709 23709 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 29697 29697 24898 24898 24898 24898 24898 24898 29697 29697 29697 29697 29697 29697 29697 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting rows in a range using Autofilter
Thanks Zack so much for your response! I have been working all night
trying to get this thing to work, but haven't been successful. I tried your idea of setting a "rngDel", but in some cases this would work and in others I would miss one of the rows which met the criteria. Just curious -- what is the logic behind setting this range (i.e., is there something about setting the AutoFilter function that requires you be one row below the first row of data?) Since I couldn't get the rngDel to work properly, I took it out and am using the following code. The other problem I have is that I keep getting the "Run time error 1004- Application Defined or object defined error" on the line below marked with an asterik (*), so for testing purposes to see if the code would work, I hard coded the criteria. Note that Oracle_no is a defined name on the "Field_Rep_Time_Sheet" sheet (Insert-Name-Define). I'm not sure why I keep getting the 1004 error. Any additional help you can give would be greatly appreciated! Connie Private Sub Check_For_Existing_Oracle_No_Click() Dim rng As Range Dim rngDel As Range Dim sh As Worksheet Dim n As Long Call ToggleEvents(False) Set sh = Worksheets("Compiled Totals") Set rng = sh.Range(sh.Cells(9, "C"), sh.Cells(sh.Rows.Count, "C").End(xlUp)) 'Set rngDel = sh.Range(sh.Cells(10, "C"), sh.Cells(sh.Rows.Count, "C").End(xlUp)) sh.AutoFilterMode = False * 'rng.AutoFilter field:=1, Criteria1:=sh.Range("oracle_no").Value rng.AutoFilter field:=1, Criteria1:="24898" VisibleDropDown = False n = rng.SpecialCells(xlCellTypeVisible).Rows.Count If n = 0 Then MsgBox "No records matched your criteria!", vbInformation, "NO ", "RECORDS " Call ErrHandle(sh) Exit Sub Else If MsgBox("Do you wish to delete " & n & " records?", vbYesNo, "DELETE " & n & " RECORDS?") < vbYes Then Call ErrHandle(sh) Exit Sub End If End If rng.SpecialCells(xlCellTypeVisible).EntireRow.Dele te MsgBox "You have successfully deleted " & n & "records for employee " & employee_name End Sub Sub ToggleEvents(blnState As Boolean) With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState = True Then .CutCopyMode = False .StatusBar = False End If End With End Sub Sub ErrHandle(sh As Worksheet) sh.AutoFilterMode = False Call ToggleEvents(True) End Sub Zack Barresse wrote: Hi there Connie, I made a few adjustments to your code. You might want to try something like this... Private Sub Check_For_Existing_Oracle_No_Click() Dim rng As Range, rngDel As Range Dim sh As Worksheet, n As Long On Error GoTo ErrHandle Call ToggleEvents(False) Set sh = Worksheets("Compiled Totals") Set rng = sh.Range(sh.Cells(9, "C"), sh.Cells(sh.Rows.Count, "C").End(xlUp)) Set rngDel = sh.Range(sh.Cells(10, "C"), sh.Cells(sh.Rows.Count, "C").End(xlUp)) sh.AutoFilterMode = False rng.AutoFilter Field:=1, Criteria1:=sh.Range("oracle_no").Value, Operator:=xlOr, Criteria2:="23356" n = rngDel.SpecialCells(xlCellTypeVisible).Rows.Count If n = 0 Then MsgBox "No records matched your criteria!", vbInformation, "NO RECORDS" GoTo ErrHandle Else If MsgBox("Do you wish to delete " & n & " records?", vbYesNo, "DELETE " & n & " RECORDS?") < vbYes Then GoTo ErrHandle End If End If rngDel.SpecialCells(xlCellTypeVisible).EntireRow.D elete ErrHandle: sh.AutoFilterMode = False Call ToggleEvents(True) End Sub Sub ToggleEvents(blnState As Boolean) With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState = True Then .CutCopyMode = False .StatusBar = False End If End With End Sub Let us know how it works for you. HTH -- Regards, Zack Barresse, aka firefytr "Connie" wrote in message ups.com... I am using the following code to delete rows in a range. Private Sub Check_For_Existing_Oracle_No_Click() Dim rng As Range Dim sh As Worksheet Set sh = Worksheets("Compiled Totals") Set rng = sh.Range(sh.Cells(9, "C"), _ sh.Cells(sh.Rows.Count, "C").End(xlUp)) rng.AutoFilter rng.AutoFilter Field:=1, Criteria1:=Range("oracle_no").Value Set rng = Sheets("Compiled Totals").Range(rng.Address).SpecialCells(xlCellTyp eVisible) rng.EntireRow.Delete Sheets("Compiled Totals").Range("C9").AutoFilter End Sub My data is as follows in column C, and the oracle_no I'm testing is 23356. The code above works, however, I not only delete all the rows in which oracle_no = 23356, but I also delete the row right above the first 23356 (where oracle_no = 23709). Is there another way to do this? The autofilter function seems a little quirky, and I want to make sure I delete the correct rows. Also, if I wanted to count the number of rows for which oracle_no = 23356, how would I do that? I want to display that value in a message prompt to the user, letting them know that they are requesting to delete x number of records. Thanks! Connie "Oracle ID #" 23709 23709 23709 23709 23709 23709 23709 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 23356 29697 29697 24898 24898 24898 24898 24898 24898 29697 29697 29697 29697 29697 29697 29697 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
| Deleting Rows Automatically using a Text File List | Excel Discussion (Misc queries) | |||
| Deleting "duplicate" rows | Excel Discussion (Misc queries) | |||
| Define a range containing the first 10 rows of a filtered list | Excel Worksheet Functions | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Linear Mode
