Results 1 to 6 of 6

Thread: Deleting Rows

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is it possible to delete more than one row at a time?

    As an example. Range("A5:A25000") has many cells which contain a dash (-). Instead of testing the contents of each cell and deleting the respective row, I thought perhaps there was a way to determine which cells contain the dash and delete all those cells at one time.

    Thanks,
    John

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using a macro, you would still have to loop through all the rows to check for the "-". It's just as easy, to delete the rows as you go.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A non macro method (I'm using 2003 - Due to limitations of the Find routine, this method will not work in earlier versions):

    Select the range you want to affect
    Ctrl F to launch Find dialog
    enter the data to find, check the match entire cell option
    Click Find All
    Highlight the results that appear
    Click Close
    Ctrl+"-" (launches delete dialog) choose Entire Row
    OK out


    I tried using the macro recorder, but the recorder did not give any useful information.

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='793410' date='15-Sep-2009 09:51']A non macro method (I'm using 2003 - Due to limitations of the Find routine, this method will not work in earlier versions):

    Select the range you want to affect
    Ctrl F to launch Find dialog
    enter the data to find, check the match entire cell option
    Click Find All
    Highlight the results that appear
    Click Close
    Ctrl+"-" (launches delete dialog) choose Entire Row
    OK out[/quote]


    You go me to think this out a little more. If one could pass the cell addresses to an array and then delete the each row of the array that may work.

    If you select a number of cells by using the Ctrl Key and left click; one can delete all rows at the same time.

    Thanks for taking another look,
    John
    Attached Images Attached Images

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In order to pass the row numbers to an array, you would still have to loop through all rows - why not delete them then? Using the Find method I described is like Ctrl+Clicking on the individual cells.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Why not add an autofilter. Filter to show the appropriate rows, then select the first row, and while holding SHIFT select the last row. When all are selected, you can delete rows to remove them and then turn off the filter...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •