+ Reply to Thread
Results 1 to 8 of 8

Delete hidden rows VBA is very slow - Please help!

  1. 01-12-2020, 12:53 PM #1
    aldenes
    aldenes is offline
    Registered User
    Join Date
    01-12-2020
    Location
    California
    MS-Off Ver
    2016
    Posts
    53

    Lightbulb Delete hidden rows VBA is very slow - Please help!

    Hello,

    I am using the following VBA to delete hidden rows; the source table is no more than 10000 entries; it's working, this is running very slow and causing Excel to become unresponsive. I think I am making a mistake by not defining last row number and instead causing it to perform the calculation too many times; but I don't know how to do that.
    Any help would be very much appreciated.
    Below is the script:
    Sub CCWF_B_DeleteHidden()
    '
    ' CCWF_B_DeleteHidden
    '

    '
    Sheets("CCWF_B").Select
    For lp = 256 To 1 Step -1
    If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
    Next
    For lp = 9999 To 1 Step -1
    If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
    Next
    Sheets("Analyzer").Select
    End Sub
    Reply With QuoteRegister To Reply
  2. 01-12-2020, 12:56 PM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Delete hidden rows VBA is very slow - Please help!

    Administrative Note:

    Welcome to the forum.

    I need to go out for a few hours. However I will reply to your post when I return if you insert the code tags.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Last edited by mehmetcik; 01-12-2020 at 01:27 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  3. 01-12-2020, 04:48 PM #3
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Delete hidden rows VBA is very slow - Please help!

    In addition to mehmetcik's comment, you could attach a excel sample file, this will help us.
    - Battle without fear gives no glory - Just try
    Reply With QuoteRegister To Reply
  4. 01-12-2020, 05:07 PM #4
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Delete hidden rows VBA is very slow - Please help!

    The code ready and waitting for you ....!
    Reply With QuoteRegister To Reply
  5. 01-13-2020, 06:26 AM #5
    daboho
    daboho is offline
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,285

    Re: Delete hidden rows VBA is very slow - Please help! 

    Please Login or Register  to view this content.
    "ThankyouFor Attention * And Your Help!!"
    Reply With QuoteRegister To Reply
  6. 01-13-2020, 06:32 AM #6
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Delete hidden rows VBA is very slow - Please help!

    @daboho
    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Reply With QuoteRegister To Reply
  7. 01-19-2020, 10:12 AM #7
    aldenes
    aldenes is offline
    Registered User
    Join Date
    01-12-2020
    Location
    California
    MS-Off Ver
    2016
    Posts
    53

    Re: Delete hidden rows VBA is very slow - Please help!

    Thank you very much!
    Reply With QuoteRegister To Reply
  8. 01-19-2020, 02:11 PM #8
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Hi,

    there is a faster way than using the Delete statement but as an attachment is missing …
    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 just the filtered rows and not the hidden ones.
    By indukp in forum Excel General
    Replies: 4
    Last Post: 09-15-2016, 11:57 AM
  3. 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
  4. [SOLVED] very slow macro (delete blank rows)
    By Danielle22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2013, 04:13 AM
  5. 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
  6. Delete Hidden Rows
    By Makeda in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 05-18-2005, 03:03 PM
  7. hidden rows & columns slow file open
    By Simon Shaw in forum Excel General
    Replies: 0
    Last Post: 04-04-2005, 08:06 PM

Tags for this Thread

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