+ Reply to Thread
Results 1 to 4 of 4

Macro causing Excel to freeze - intended to hide cells with zero value

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    Oakland, CA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Exclamation Macro causing Excel to freeze - intended to hide cells with zero value

    Hey all,

    My apologies in advance if this has been covered elsewhere, but I am very new to macros and am in need of a quick resolution to this.

    I have pieced together the following code (see below) that is intended to hide cells with zero values. Essentially, it is in an invoice spreadsheet that shows the number of employees who worked a certain interval of time, i.e. the recipient could look at it and say there were X cashiers working from 2pm to 5pm, Y cashiers from 3pm to 5:30pm, Z cashiers from 3:30pm to 7pm, so on and so forth for every possible 15 minute increment of time.

    As you can imagine, this involves a lot of cells (about 36k rows) that often don't have a value, and I need a way to hide them when the value is zero. This particular string of code has worked in the past, but for some reason it will only work once. If I try and run it a second time, Excel freezes up and I'm essentially screwed.

    Here's the code. I'm running Excel 2010 on Windows 7. Again, I'm very new to this and have only made it this far by Googling and copy/pasting bits of code.


    Please Login or Register  to view this content.
    Any help would be greatly appreciated! Thanks in advance!
    Last edited by Leith Ross; 09-25-2012 at 05:21 PM. Reason: Added Code Tags

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,885

    Re: Macro causing Excel to freeze - intended to hide cells with zero value

    Wow! That macro can't be fast looping through all those cells in VBA. Can't you just use Autofilter?
    Or if the changes in the sheet just affects the same row as the cell you change then the macro only needs to check that row. I think there has been posts like that recently.
    Found it!
    http://www.excelforum.com/excel-prog...ero-value.html
    Sample workbook?

  3. #3
    Registered User
    Join Date
    09-25-2012
    Location
    Oakland, CA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Macro causing Excel to freeze - intended to hide cells with zero value

    Sorry Jacc, but I'm a complete newbie to Macros/VBAs and to this forum. Maybe I can use Autofilter--I have no idea what it is or how I would do it. If you tell me how to insert a sample workbook I'll see if I can put one together.

    Thanks, and sorry for my lack of competence!

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,885

    Re: Macro causing Excel to freeze - intended to hide cells with zero value

    No worries. This is what I mean by autofilter and it is sort of the "manual" method of doing it.
    Autofilter, filtering for empty cells in a column.jpg

    What about the link to the other thread, anything there that would work?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1