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 |
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 |