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]

Thanks for including the extra information and showing the workbook events.
I was a bit concerned about the protection but somewhat surprisingly having the protection "on" did not stop it from working.

Can you confirm that on the Ecole sheet that the number 1 appears in Cell A11 and that your formula in column A returns ""
if the row number is > than what you have in E13 ?

I prefer using debug.print but are you more comfortable with having a message box ?
In the worksheet_change module BEFORE the "End If" can you add this line:

        MsgBox "Performed the Worksheet Events"

In the HideShowRows module, just BEFORE the "End SUB" can you add this line:-

    MsgBox "Sheet= " & sht.Name & vbLf & "rngHide= " & rnghide.Address & vbLf & "rngShow= " & rngshow.Address

OK......I included a mini sheet so you can see what is in cell A11 and added the extra line in the 2 codes but nothing is happening.

Silly question: When I'm in visual basic can I run the HideShowRows ? Cause If I try to run it I don't see it in the list (see image below)

Demo.xlsm
	A	B	C	D	E	F	G	H
9		Nom de l'élève	Année d'étude	Anomalie	Horaire personnalisé (retrait partiel de la classe <50%)	Est-ce que l'élève est à l'école à temps partiel ?	PEI	Conditions grave
10		ex: Prénom Nom	6e année	Associées	Non	Non	Oui	Trouble du langage, TSA
11	1
12	2
13	3
14	4
15	5
16	6
17	7
18	8
19	9
20	10
21	11
22	12
23
24
25
26
27
28
29
30
31
32
Ecole

Cell Formulas
Range	Formula
C9	C9	="Année d'étude " &Accueil!$H$10
A11	A11	=IFERROR(IF(Accueil!E13="","",1),"")
A12:A32	A12	=IFERROR(IF(A11+1>Accueil!E$13,"",Ecole!A11+1),"")

Cells with Data Validation
Cell	Allow	Criteria
C11:C110	List	= Niveau
D11:D110	List	= Anomalies
E11:G110	List	= OuiNon

 

[pijan11]

Thanks for including the extra information and showing the workbook events.
I was a bit concerned about the protection but somewhat surprisingly having the protection "on" did not stop it from working.

Can you confirm that on the Ecole sheet that the number 1 appears in Cell A11 and that your formula in column A returns ""
if the row number is > than what you have in E13 ?

I prefer using debug.print but are you more comfortable with having a message box ?
In the worksheet_change module BEFORE the "End If" can you add this line:

        MsgBox "Performed the Worksheet Events"

In the HideShowRows module, just BEFORE the "End SUB" can you add this line:-

    MsgBox "Sheet= " & sht.Name & vbLf & "rngHide= " & rnghide.Address & vbLf & "rngShow= " & rngshow.Address

CORRECTION......I just tried it on a windows pc and it is working......I was working on a mac so not sure why it is not working on mac

 

[pijan11]

Thanks for including the extra information and showing the workbook events.
I was a bit concerned about the protection but somewhat surprisingly having the protection "on" did not stop it from working.

Can you confirm that on the Ecole sheet that the number 1 appears in Cell A11 and that your formula in column A returns ""
if the row number is > than what you have in E13 ?

I prefer using debug.print but are you more comfortable with having a message box ?
In the worksheet_change module BEFORE the "End If" can you add this line:

        MsgBox "Performed the Worksheet Events"

In the HideShowRows module, just BEFORE the "End SUB" can you add this line:-

    MsgBox "Sheet= " & sht.Name & vbLf & "rngHide= " & rnghide.Address & vbLf & "rngShow= " & rngshow.Address

So now my last question is how to we get it to work without having a message box?

 

[Alex Blakenburg]

The message box does not affect the operation of the macro, so just delete the 2 msgbox lines.
ie the one in the Worksheet_Change module and
the one in the HideSheet module. (unless you think you might need them again then just comment them out by putting a single quote (') in front of the line)

Did it resolve the speed issue ?

 

[pijan11]

The message box does not affect the operation of the macro, so just delete the 2 msgbox lines.
ie the one in the Worksheet_Change module and
the one in the HideSheet module. (unless you think you might need them again then just comment them out by putting a single quote (') in front of the line)

Did it resolve the speed issue ?

I was able to test it once and the speed was fine. But now i'm trying to test it again and i'm getting this error

If I click on Debug, this is what I get from the code in module:

 

[Alex Blakenburg]

Change both the lines in your screenshot to this:

    If Not rnghide Is Nothing Then rnghide.EntireRow.Hidden = True
    If Not rngshow Is Nothing Then rngshow.EntireRow.Hidden = False

 

[pijan11]

Change both the lines in your screenshot to this:

    If Not rnghide Is Nothing Then rnghide.EntireRow.Hidden = True
    If Not rngshow Is Nothing Then rngshow.EntireRow.Hidden = False

i'm still getting the error message

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel running slow due to macros
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[pijan11]

i'm still getting the error message

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

 

[pijan11]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel running slow due to macros
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thank you for letting me know......I was unaware. No valid solution was provided on other forums so I did go to the other forums and added a link to this one. I was in a crunch to present my file to my employer so this is why I posted in different forums. It's noted. But I am glad to say that this is where I found the most help. Thanks MrExcel