Excel running slow

[pijan11]

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

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
	B	C	D	E	F	G	H	I	J	K	L	M	N
8
9
10	École:					Année scolaire:
11
12
13			Nombre d'élèves:	5
14
15
16		Nom 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
Cell	Allow	Criteria
H10:H14	List	= Année
C10:E10	List	= Écoles
E13	List	= Nombre

Mini sheet 2

Écoles élémentaires - Dotation AE 2021-2022.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
8
9
10	École:					Année scolaire:
11
12
13			Nombre d'élèves:	5
14
15
16		Nom 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
Cell	Allow	Criteria
H10:H14	List	= Année
C10:E10	List	= Écoles
E13	List	= Nombre

 

[pijan11]

oops....not the same error but this one

View attachment 56650

I did get it to work and is working wonderfully now......Thanks @Alex Blakenburg for all your help

 

[Fluff]

Can you please post links to any other sites where you have asked this question. Thanks

 

[pijan11]

Can you please post links to any other sites where you have asked this question. Thanks

Excel running slow due to macros
Excel running slow due to macros

 

[Fluff]

Thanks for that.

 

[pijan11]

Thanks for that.

from now on i'm sticking with this forum.....I find this site to be way more user friendly then the other ones.....Good job to all moderators

 

[pijan11]

@Alex Blakenburg one last thing (I Hope) If I return to cell E13 and delete the number previously inputed in that cell and leave the cell blank I get this error:

Is there something we can add so this doesn't happen ?

 

[Alex Blakenburg]

I don't understand, the message box was only there for troubleshooting and that you were going to remove it now that you had it working. (post #23 & #24)
It is pretty much the last thing the code does, so unless you still have a need for it, simply delete or comment out that line.

If you really wanted to keep it we would need to cater for "is nothing" on the 2 range names rngHide & rngShow like we did on the previous 2 lines of code.

 

[pijan11]

I don't understand, the message box was only there for troubleshooting and that you were going to remove it now that you had it working. (post #23 & #24)
It is pretty much the last thing the code does, so unless you still have a need for it, simply delete or comment out that line.

If you really wanted to keep it we would need to cater for "is nothing" on the 2 range names rngHide & rngShow like we did on the previous 2 lines of code.

You’re totally right. I lost track of why this error was happening. My bad. Thank you for every thing.

 

[Alex Blakenburg]

My pleasure. Happy to help.