Excel running slow

P

pijan11

New Member
Joined
Jan 29, 2022
Messages
29
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I'm new to VBA and I need some help with speed being slow when inputing data into my file (sorry for my data being in French). When I input data in the Fields entitled "École", "Année scolaire", i can't do anything for about 4 seconds. When I input data into the field "Nombre d'élèves", and I click on the big yellow button to get to another sheet (2nd mini sheet) then the number of lines showing should match the number inputed on the first sheet by using this code (I have another similar sheet using the same code that is password locked which you can access by clicking on the left green button on mini sheet 1, but this sheet is running a little faster) and the some formulas in the sheet itself (see below)

CODE

VBA Code: 
Private Sub Worksheet_Calculate()
       
    Dim c As Range
        
    Application.EnableEvents = False
    
    
        For Each c In Range("A11:A110") '<====Input your range here
        
            If c.Value = "" Then
                Rows(c.Row & ":" & c.Row).EntireRow.Hidden = True
            Else
                Range(c.Row & ":" & c.Row).EntireRow.Hidden = False
            End If
            
        Next

    Application.EnableEvents = True

End Sub

Formula
=IFERROR(IF(Accueil!E13="","",1),"")
=IFERROR(IF(A11+1>Accueil!E$13,"",Ecole!A11+1),"")

where "Ecole" is the active sheet (mini sheet 2) and "Accueil!E$13" is the cell I inputed the numbre 5 in mini sheet 1

I then manually input data in the other columns in mini sheet 2 and here is where it gets really slow, about 15 seconds before I can click in another cell.

I must say that the data inputed into mini sheet 2 is beeing copied to 3 other sheets.

Any help would be appreciated

Mini sheet 1

Écoles élémentaires - Dotation AE 2021-2022.xlsm
BCDEFGHIJKLMN
8
9
10École:Année scolaire:
11
12
13Nombre d'élèves: 5
14
15
16Nom de la personne ou des personnes qui ont contribués aux données fournies dans ce fichier:
17
18
19
20
21
22
Accueil
Cells with Data Validation
CellAllowCriteria
H10:H14List= Année
C10:E10List= Écoles
E13List= Nombre


Mini sheet 2

Écoles élémentaires - Dotation AE 2021-2022.xlsm
BCDEFGHIJKLMN
8
9
10École:Année scolaire:
11
12
13Nombre d'élèves: 5
14
15
16Nom de la personne ou des personnes qui ont contribués aux données fournies dans ce fichier:
17
18
19
20
21
22
Accueil
Cells with Data Validation
CellAllowCriteria
H10:H14List= Année
C10:E10List= Écoles
E13List= Nombre
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Oops here is mini sheet 2 and the button that i'm referring too in my previous post which are found on mini sheet 1

Écoles élémentaires - Dotation AE 2021-2022.xlsm
ABCDEFGH
9Nom de l'élèveAnnée d'étude AnomalieHoraire personnalisé (retrait partiel de la classe <50%)Est-ce que l'élève est à l'école à temps partiel ?PEIConditions grave
10ex: Prénom Nom6e annéeAssociéesNonNonOuiTrouble du langage, TSA
111
122
133
144
155
Ecole
Cell Formulas
RangeFormula
C9C9="Année d'étude " &Accueil!$H$10
A11A11=IFERROR(IF(Accueil!E13="","",1),"")
A12:A15A12=IFERROR(IF(A11+1>Accueil!E$13,"",Ecole!A11+1),"")
Cells with Data Validation
CellAllowCriteria
C11:C110List= Niveau
D11:D110List= Anomalies
E11:G110List= OuiNon
 

Attachments

  • Screen Shot 2022-01-30 at 3.57.39 PM.png
    Screen Shot 2022-01-30 at 3.57.39 PM.png
    208.9 KB · Views: 9
Upvote 0
How about some code like:

VBA Code: 
Sub TestHideShowRows()
'
    Application.ScreenUpdating = False                                                                              ' Turn ScreenUpdating off
    Application.Calculation = xlManual                                                                              ' Turn off Calculations
    Application.EnableEvents = False                                                                                ' Turn EnableEvents off
'
    Dim ArrayRow        As Long
    Dim LastRowToTest   As Long, StartRowToTest As Long
    Dim rnghide         As Range, rngshow       As Range
    Dim ColumnToTest    As String
    Dim ColumnArray     As Variant
'
    ColumnToTest = "A"                                                                                              ' <--- Set this to the column to test
    StartRowToTest = 11                                                                                             ' <--- Set this to the start row to test
    LastRowToTest = 115                                                                                             ' <--- Set this to the last row to test
'
    Set rnghide = Nothing
    Set rngshow = Nothing
'
    ColumnArray = Range(ColumnToTest & "1:" & ColumnToTest & LastRowToTest).Value                                   ' Load column values into ColumnArray
'
    For ArrayRow = StartRowToTest To LastRowToTest                                                                  ' Loop to check each cell in column range
      If ColumnArray(ArrayRow, 1) = vbNullString Then                                                               '   If blank cell found then ...
        If rnghide Is Nothing Then Set rnghide = Rows(ArrayRow) Else Set rnghide = Union(rnghide, Rows(ArrayRow))   '       Save row # to rnghide
      Else                                                                                                          '   Else
        If rngshow Is Nothing Then Set rngshow = Rows(ArrayRow) Else Set rngshow = Union(rngshow, Rows(ArrayRow))   '       Save row # to rngshow
      End If
    Next                                                                                                            ' Loop back for next cell
'
    rnghide.EntireRow.Hidden = True                                                                                 ' Hide all rows to be hidden
    rngshow.EntireRow.Hidden = False                                                                                ' Show all rows to be shown
'
    Application.EnableEvents = True                                                                                 ' Turn EnableEvents back on
    Application.Calculation = xlAutomatic                                                                           ' Turn Calculations back on
    Application.ScreenUpdating = True                                                                               ' Turn ScreenUpdating back on
End Sub
 
Upvote 0
Better yet, In my last code ...

replace:
VBA Code: 
    rnghide.EntireRow.Hidden = True                                                                                 ' Hide all rows to be hidden
    rngshow.EntireRow.Hidden = False                                                                                ' Show all rows to be shown

with:
VBA Code: 
    If Not rnghide Is Nothing Then rnghide.EntireRow.Hidden = True                                                  ' Hide all rows to be hidden
    If Not rngshow Is Nothing Then rngshow.EntireRow.Hidden = False                                                 ' Show all rows to be shown
 
Upvote 0
Here is a different version of code that uses filtering:

VBA Code: 
Sub TestHideShowRowsV2()
'
    Application.ScreenUpdating = False                                                                              ' Turn ScreenUpdating off
    Application.Calculation = xlManual                                                                              ' Turn off Calculations
    Application.EnableEvents = False                                                                                ' Turn EnableEvents off
'
    Dim LastRowToTest   As Long, StartRowToTest As Long
    Dim rngColA         As Range
    Dim rnghide         As Range
    Dim ColumnToTest    As String
    Dim wsSource        As Worksheet
'
    Set wsSource = Sheets("Sheet1")                                                                                 ' <--- Set this to the source sheet name
    ColumnToTest = "A"                                                                                              ' <--- Set this to the column to test
    StartRowToTest = 11                                                                                             ' <--- Set this to the start row to test
    LastRowToTest = 115                                                                                             ' <--- Set this to the last row to test
'
    Sheets("Sheet1").AutoFilterMode = False                                                                         ' Remove any current filters on sheet
    Range(StartRowToTest & ":" & LastRowToTest).EntireRow.Hidden = False                                            ' Unhide all rows in the desired range
'
    Set rngColA = Range(ColumnToTest & StartRowToTest & ":" & ColumnToTest & LastRowToTest - 1)                     ' Set the range to be filtered for blanks
'
    rngColA.AutoFilter Field:=1, Criteria1:="="                                                                     ' Filter all blank rows in range
    Set rnghide = rngColA.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow                                    ' Save rows to be deleted into rnghide
'
    Sheets("Sheet1").AutoFilterMode = False                                                                         ' Turn filter mode off
'
    If Not rnghide Is Nothing Then rnghide.EntireRow.Hidden = True                                                  ' Hide all rows to be hidden
'
    Application.EnableEvents = True                                                                                 ' Turn EnableEvents back on
    Application.Calculation = xlAutomatic                                                                           ' Turn Calculations back on
    Application.ScreenUpdating = True                                                                               ' Turn ScreenUpdating back on
End Sub
 
Last edited:
Upvote 0
@pijan11, see how you go with johnny's code but I would be concerned that there is a lot more going on that is causing your issues.
You have your code in a Worksheet_Calculate event. Since you have buttons is it necessary to have it as an event, this is likely to be running much more frequently than required.
You mainly talk about sheet1 and sheet 2 but then also mention 3 more sheets.
pijan11 said:
the data inputed into mini sheet 2 is beeing copied to 3 other sheets.
Click to expand...
This makes 5 sheets, how many of these have worksheet events in their code window and what is the code in each one ?
Is the copying based on a button or using a worksheet event ?
 
Upvote 0
Alex Blakenburg said:
@pijan11, see how you go with johnny's code but I would be concerned that there is a lot more going on that is causing your issues.
You have your code in a Worksheet_Calculate event. Since you have buttons is it necessary to have it as an event, this is likely to be running much more frequently than required.
You mainly talk about sheet1 and sheet 2 but then also mention 3 more sheets.

This makes 5 sheets, how many of these have worksheet events in their code window and what is the code in each one ?
Is the copying based on a button or using a worksheet event ?
Click to expand...
Hi Alex. Thanks for replying. Any feedback is appreciated. I have three sheets using the same Worksheet_Calculate event but with their own range of hidden/visible rows. The copying is not done based on a button or code, it is done by formulas in the spreadsheet itself.
 
Upvote 0
johnnyL said:
Here is a different version of code that uses filtering:

VBA Code: 
Sub TestHideShowRowsV2()
'
    Application.ScreenUpdating = False                                                                              ' Turn ScreenUpdating off
    Application.Calculation = xlManual                                                                              ' Turn off Calculations
    Application.EnableEvents = False                                                                                ' Turn EnableEvents off
'
    Dim LastRowToTest   As Long, StartRowToTest As Long
    Dim rngColA         As Range
    Dim rnghide         As Range
    Dim ColumnToTest    As String
    Dim wsSource        As Worksheet
'
    Set wsSource = Sheets("Sheet1")                                                                                 ' <--- Set this to the source sheet name
    ColumnToTest = "A"                                                                                              ' <--- Set this to the column to test
    StartRowToTest = 11                                                                                             ' <--- Set this to the start row to test
    LastRowToTest = 115                                                                                             ' <--- Set this to the last row to test
'
    Sheets("Sheet1").AutoFilterMode = False                                                                         ' Remove any current filters on sheet
    Range(StartRowToTest & ":" & LastRowToTest).EntireRow.Hidden = False                                            ' Unhide all rows in the desired range
'
    Set rngColA = Range(ColumnToTest & StartRowToTest & ":" & ColumnToTest & LastRowToTest - 1)                     ' Set the range to be filtered for blanks
'
    rngColA.AutoFilter Field:=1, Criteria1:="="                                                                     ' Filter all blank rows in range
    Set rnghide = rngColA.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow                                    ' Save rows to be deleted into rnghide
'
    Sheets("Sheet1").AutoFilterMode = False                                                                         ' Turn filter mode off
'
    If Not rnghide Is Nothing Then rnghide.EntireRow.Hidden = True                                                  ' Hide all rows to be hidden
'
    Application.EnableEvents = True                                                                                 ' Turn EnableEvents back on
    Application.Calculation = xlAutomatic                                                                           ' Turn Calculations back on
    Application.ScreenUpdating = True                                                                               ' Turn ScreenUpdating back on
End Sub
Click to expand...
@johnnyL Thanks for your reply. I tried this code and it is not doing what i'm looking for. I appreciate the time you took to try and help me.
 
Upvote 0
Does the sheet that you are using to input the data have the hide/unhide code in it ?

Do the other sheets need to run the hide/unhide every time you change something on the input sheet or only when you go to have a look at the sheet ? Would changing their events to a Worksheet_Activate achieve what you need ?
(do you input directly into the other sheets ?)
 
Upvote 0
pijan11 said:
@johnnyL Thanks for your reply. I tried this code and it is not doing what i'm looking for. I appreciate the time you took to try and help me.
Click to expand...

What did it not do? The code, as in your original post, checks the column range specified for blanks and if/when the cell is blank, the row is hidden, otherwise the row is shown.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
open a file based on VLookUp result
Replies
0
Views
128
Flavien
F
J
  • Question
Excel crashing when entering date in userform
Replies
0
Views
790
jptaz
J
N
  • Question
Excel Macro VBA - Subtotal function issue
Replies
1
Views
854
Takae
T
A
  • Question
Fonction UNIQUE() avec liste comprenant des cellules vides
Replies
7
Views
8K
Fluff
Fluff
P
  • Question
Macro with Pivot Tables working (sometimes) the first time, and then never again
Replies
5
Views
3K
RoryA
RoryA

Forum statistics

Threads
1,215,248
Messages
6,123,877
Members
449,130
Latest member
lolasmith

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top