+ Reply to Thread
Results 1 to 8 of 8

Can't delete hidden rows in bulk

  1. 08-03-2015, 09:46 AM #1
    forrestgump1980
    forrestgump1980 is offline
    Forum Contributor
    Join Date
    06-06-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    180

    Can't delete hidden rows in bulk

    Hi there,

    Is there any way I can delete hidden rows in bulk. I wrote some code to do this line by line in a loop, however, this was way to slow as there are over 25,000 rows of data in my spread sheet.

    Any help would be greatly appreciated.

    Regards,

    Forrestgump1980
    Reply With QuoteRegister To Reply
  2. 08-03-2015, 09:56 AM #2
    bmouse
    bmouse is offline
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Can't delete hidden rows in bulk

    Perhaps it is because of the code being too complex?
    I have a code of my own, care to try?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 08-03-2015, 10:12 AM #3
    StuCram
    StuCram is offline
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: Can't delete hidden rows in bulk

    A potential problem with the solution by 'bmouse' is that the rows are renumbered whenever one of them is deleted.
    If row 9 is hidden, it gets deleted and then row 10 becomes row 9 but the loop will move on to the new row 10, skipping the previous row 10 which is now row 9. Does that make sense?

    The cure for this is to work backwards or from the bottom up.

    It will be speedier too when screen updating is turned off

    Please Login or Register  to view this content.
    If this has been helpful, please click on the star at the left.
    Reply With QuoteRegister To Reply
  4. 08-03-2015, 10:22 AM #4
    forrestgump1980
    forrestgump1980 is offline
    Forum Contributor
    Join Date
    06-06-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Can't delete hidden rows in bulk

    Hi guys,

    Thanks for this but unfortunately I have tried this method already and it proves very slow when working with such a large number of rows (Over 25,000) therefore was trying to find a way of doing it quickly in bulk.

    Regards,

    Forrestgump1980
    Reply With QuoteRegister To Reply
  5. 08-03-2015, 10:31 AM #5
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,373

    Re: Can't delete hidden rows in bulk

    Maybe this:

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  6. 08-03-2015, 10:46 AM #6
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Can't delete hidden rows in bulk

    Maybe:

    Please Login or Register  to view this content.
    Last edited by JOHN H. DAVIS; 08-03-2015 at 10:48 AM.
    Reply With QuoteRegister To Reply
  7. 08-03-2015, 10:48 AM #7
    Crispy85
    Crispy85 is offline
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Can't delete hidden rows in bulk

    TMS Solution would work well.
    Or perhaps copy the visible cells (with SpecialCells(xlCellTypeVisible) and paste in a new sheet.
    Reply With QuoteRegister To Reply
  8. 08-03-2015, 11:03 AM #8
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,373

    Re: Can't delete hidden rows in bulk

    I think John's solution would work well for removing the rows in place, and Crispy85's suggestion would be good if you want to move the data to another sheet.

    Be interesting to time the different approaches.

    Regards, TMS
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. delete filtered rows without deleting the hidden rows in Excel 2010?
    By jdallas in forum Excel General
    Replies: 10
    Last Post: 06-07-2018, 04:16 PM
  2. Delete only Filtered Rows not hidden rows in excel 2001
    By Anupma in forum Excel General
    Replies: 1
    Last Post: 04-08-2014, 02:50 AM
  3. Any faster way to delete hidden rows of data
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-20-2012, 02:58 PM
  4. How to delete multiple rows without deleting the hidden rows when filtered?
    By geng in forum Excel General
    Replies: 4
    Last Post: 06-11-2010, 03:29 PM
  5. delete all blank rows on a hidden sheet
    By FRIEL in forum Excel General
    Replies: 1
    Last Post: 09-10-2008, 12:32 PM
  6. Delete hidden rows & columns
    By tommy_gtr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-04-2007, 06:12 AM
  7. Delete Hidden Rows
    By Makeda in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 05-18-2005, 03:03 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1