Results 1 to 6 of 6

Hide Row on cell value - TOO SLOW- SCREEN STUTTERS AND PIXILATES

This is a discussion on Hide Row on cell value - TOO SLOW- SCREEN STUTTERS AND PIXILATES within the Excel Questions forums, part of the Question Forums category; Hey everyone, I have a macro which hides cells based on cell value "0". The program is a calculator with ...

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    39

    Default Hide Row on cell value - TOO SLOW- SCREEN STUTTERS AND PIXILATES

    Hey everyone,

    I have a macro which hides cells based on cell value "0". The program is a calculator with a variety of options. Depending on the option the user selects, it will be "0" or "1" so when the copy command is clicked, it will hide the unnecessary rows.

    However when it switches between the 2 sheets, it is too slow, and there is visible stutter, while the rows hide and it switches back and forth.

    I previously used 5 different macro's that would be triggered depending on selection, but this resulted in too much code. I am looking for a simplistic way to have 1 code, for all options like the below.

    Can anyone suggest a solution to make this process faster to minimize the screen stuttering the way it does?

    Code:
    Sub HideRows()    Sheets("TD file notes").Select
        Dim cell As Range
        For Each cell In Range("A1:A63")
            If Not IsEmpty(cell) Then
                If cell.Value = 0 Then
                    cell.EntireRow.Hidden = True
                    Call copy
                Sheets("TD Payment Calculator").Select
                Range("G5").Select
    
                End If
            End If
        Next
    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2016
    Location
    UK
    Posts
    160

    Default Re: Hide Row on cell value - TOO SLOW- SCREEN STUTTERS AND PIXILATES

    try this

    Code:
    Sub HideRows()    Sheets("TD file notes").Select
        Dim cell As Range
    
    Application.screenupdating=false
        For Each cell In Range("A1:A63")
            If Not IsEmpty(cell) Then
                If cell.Value = 0 Then
                    cell.EntireRow.Hidden = True
                    Call copy
                Sheets("TD Payment Calculator").Select
                Range("G5").Select
    
                End If
            End If
        Next 
    Application.Screenupdating=true
    End Sub
    
    Last edited by DaveBlakeMAAT; Aug 11th, 2016 at 12:52 PM.
    Regards

    Dave Blake MAAT
    (Member of the Association of Accounting Technicians )

  3. #3
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    451

    Default Re: Hide Row on cell value - TOO SLOW- SCREEN STUTTERS AND PIXILATES

    To expand on Dave's response, Analyst Cave has a nice group of code (OptimizeVBA) that I've incorporated into almost all my code that addresses these issues.

    Guide to Improving VBA Performance. Faster Excel VBA

    hth

  4. #4
    New Member
    Join Date
    Apr 2012
    Posts
    39

    Default Re: Hide Row on cell value - TOO SLOW- SCREEN STUTTERS AND PIXILATES

    i didn't realize i could do that! it works like a charm! thanks buddy
    Last edited by hensleyj; Aug 11th, 2016 at 08:11 PM.

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    39

    Default Re: Hide Row on cell value - TOO SLOW- SCREEN STUTTERS AND PIXILATES

    thats a great link, thanks heaps!

  6. #6
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    451

    Default Re: Hide Row on cell value - TOO SLOW- SCREEN STUTTERS AND PIXILATES

    My pleasure!!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com