CODEWITHSUNDEEP
excelvba
Anesh
Anesh

Reputation: 33

How to speed up this vb code in Excel

I am using the following code in an Excel sheet for calculation. Unfortunately it is taking too much time to calculate and the page keeps flickering.

    Private Sub Worksheet_Activate() 
    BeginRow = 1  
    EndRow = 300
    ChkCol = 3

     For RowCnt = BeginRow To EndRow
     If Cells(RowCnt, ChkCol).Value = "B" Then
     Cells(RowCnt, ChkCol).EntireRow.Hidden = True
     Else
     Cells(RowCnt, ChkCol).EntireRow.Hidden = False
     End If
     Next RowCnt     
     End Sub

It is for an attendance management software and there are 4 sheets. I am using this code for all sheets except in Sheet1. Master data is entered in sheet1. Please help me to speed up this process.

Upvotes: 1

Views: 169

Answers (3)

Gaijinhunter
Gaijinhunter

Reputation: 14685

Constantly calling up Excel is expensive. Dumping the data you need to check into a variant array and working on that is much faster. Also as mentioned, you need to turn off screenupdating, otherwise calculations will be occuring left and right, speeding things down considerbaly.

Here's how to use a variant array in the context you are using (hide rows with "B" in column C, unhide if not "B"):

Sub improvedSpeed()

Application.ScreenUpdating = False
Dim varray() As Variant
Dim i As Long

varray() = Range("C1:C300").Value

For i = 1 To 300
    If varray(i, 1) = "B" Then
        Rows(i).Hidden = True
    Else
        Rows(i).Hidden = False
    End If
Next

Application.ScreenUpdating = True

End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149295

I agree with @BK201 that you can also use autofilter. Here is another faster method. I say faster because it doesn't show/hide the rows in a loop. Additionally you can sandwhich your code between Application.ScreenUpdating = false and Application.ScreenUpdating = true to stop the flickering as @KazJaw suggested.

Option Explicit

Private Sub Worksheet_Activate()
    Dim BeginRow As Long, EndRow As Long
    Dim ChkCol As Long, RowCnt As Long
    Dim rngHide As Range

    BeginRow = 1: EndRow = 300: ChkCol = 3

    '~~> Unhide all the rows
    Rows("1:300").EntireRow.Hidden = False

    '~~> Loop through the rows and identify which rows needs to be hidden
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "B" Then
            If rngHide Is Nothing Then
                Set rngHide = Rows(RowCnt)
            Else
                Set rngHide = Union(rngHide, Rows(RowCnt))
            End If
        End If
    Next RowCnt

    '~~> Hide the rows  in one go
    If Not rngHide Is Nothing Then rngHide.EntireRow.Hidden = True
End Sub

Upvotes: 3

simple_code
simple_code

Reputation: 767

I just quickly looked at your problem. It seems hiding an entire row takes a lot of time. So you need to check that a row is not hidden already before you hide it. Or already showing if you want to unhide it. I think this code will fix the problem

Private Sub Worksheet_Activate()
BeginRow = 1
EndRow = 300
ChkCol = 3

 For RowCnt = BeginRow To EndRow
 If Cells(RowCnt, ChkCol).Value = "B" Then
    If Cells(RowCnt, ChkCol).EntireRow.Hidden = False Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    End If
 Else
    If Cells(RowCnt, ChkCol).EntireRow.Hidden = True Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    End If
 End If
 Next RowCnt
 End Sub

Your code also ran slowly on my computer but this code worked for me.

Upvotes: 0

Related Questions