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]

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 ?)

I'll try to explain myself a different way. On sheet 1 I enter a number and when I do so i have a formula in sheets 2 and 3 that should unhide the number of rows corresponding to the number i've inputted into sheet 1 (ex., I input 5 in cell E13 on sheet one, then on sheet 2 and 3, in the A column, in cell A11, A12 and so on i have the following formula :
=IFERROR(IF(Sheet1l!E13="","",1),"")
=IFERROR(IF(A11+1>Sheet1!E$13,"",Sheet2!A11+1),"")
so this insert the number in the column A from 1 to 5, then the code is telling it to unhide the rows that have a value in the A column.

The 3 sheets that I am using the hide/unhide code, I am also inputting data into them that is copied on other sheets.

So I guess the sheets don't need to run the hide/unhide code everytime a change data on them.....only when i change the number in cell E13 on Sheet 1.

I think this might be causing the file beeing slow as it is calcuclating every single time I input data into those sheet.

How do I make it so it only runs when I input or change cell E13 on Sheet 1

 

[pijan11]

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 ?)

Let me try explaining it another way.
Sheet 2 and 3 are replicas
On sheet 1 in cell E13 i input a number, for example, 5
then on sheets 2 and 3 in cell A11:A100 I have the following formula:
=IFERROR(IF(Sheet1l!E13="","",1),"")
=IFERROR(IF(A11+1>Sheet1!E$13,"",Sheet2!A11+1),""), and so on
This input a number in cell A11:A100(in both Sheet 2 and Sheet 3), so A11 =1, A12 = 2, A13 = 3 , until the number in cell E13 of sheet 1 is reached.

Then the code to unhide the rows with a value in cell A11:A100 is applied.

I then input data in the in the other cells in Sheet 2 which is being copied, by formula on the spreadsheet itslef, to Sheet 3, Sheet 4 and Sheet 5
I then input some more data in Sheet 3 that is copied to Sheet 4. The data now in sheet 4 (from sheet 2 and sheet 3 ) is being used to calculate averages and those results are copied into Sheet 5.

Then on Sheet 5 i'm using the unhide code if there is data in cells B2:B101 (which the data that's been copied from Sheet 2, ex. name)

I think the problem is that every time I input data in any sheet the 3 hide/unhide code runs which is why it gets really slow,

How do I make it so the code only applies when I enter a value in cell E13 from Sheet 1 and then when i input the name in cells in column B in sheet 2 that is then being copied to sheet 5 and the rows on sheet five is unhidden by a code because there is something in cells B2:B:101.

I hope that it is clearer now

 

[pijan11]

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.

it wouldn't hide or unhide the row. it added a filter in the top cell, that's it

 

[Alex Blakenburg]

Try this on a copy of your workbook. I tried to stick with @johnnyL's code.

1) Remove the Worksheet_Calculate code from each sheet (assuming it only relates to the Hide/Unhide function)
2) in the code module of the sheet in which you update E13 add the below:-
It needs the Call line for Each sheet in which you need to hide/unhide rows, changing the sheet name, column, and rows to suit.
3) In a standard module put the second lot of code which is a modified version of Johnny's code,

Private Sub Worksheet_Change(ByVal Target As Range)

'
    Application.ScreenUpdating = False                                                                              ' Turn ScreenUpdating off
    Application.Calculation = xlManual                                                                              ' Turn off Calculations
    Application.EnableEvents = False                                                                                ' Turn EnableEvents off
                                                                               
    If Not Intersect(Target, Range("E13")) Is Nothing Then
        ' XXX Add a Call line for each sheet needing the Row Hide/Unhide applied and change parameters to suit
        Call HideShowRows(shtName:="Sheet2", ColumnToTest:="A", StartRowToTest:=11, _
                            LastRowToTest:=110)
        Call HideShowRows(shtName:="Sheet3", ColumnToTest:="A", StartRowToTest:=11, _
                            LastRowToTest:=110)
    End If

'
    Application.EnableEvents = True                                                                                 ' Turn EnableEvents back on
    Application.Calculation = xlAutomatic                                                                           ' Turn Calculations back on
    Application.ScreenUpdating = True                                                                               ' Turn ScreenUpdating back on

End Sub

3) for the standard module called by worksheet event.

Sub HideShowRows(shtName As String, ColumnToTest As String, StartRowToTest As Long, _
                    LastRowToTest As Long)
'
    Application.ScreenUpdating = False                                                                              ' Turn ScreenUpdating off
    Application.Calculation = xlManual                                                                              ' Turn off Calculations
    Application.EnableEvents = False                                                                                ' Turn EnableEvents off
'
    Dim ArrayRow        As Long
    Dim rnghide         As Range, rngshow       As Range
    Dim ColumnArray     As Variant
    Dim sht             As Worksheet
'
    Set sht = Worksheets(shtName)
'
    Set rnghide = Nothing
    Set rngshow = Nothing
'
    ColumnArray = sht.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 = sht.Rows(ArrayRow) Else Set rnghide = Union(rnghide, sht.Rows(ArrayRow))   '       Save row # to rnghide
      Else                                                                                                          '   Else
        If rngshow Is Nothing Then Set rngshow = sht.Rows(ArrayRow) Else Set rngshow = Union(rngshow, sht.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

 

[pijan11]

Try this on a copy of your workbook. I tried to stick with @johnnyL's code.

1) Remove the Worksheet_Calculate code from each sheet (assuming it only relates to the Hide/Unhide function)
2) in the code module of the sheet in which you update E13 add the below:-
It needs the Call line for Each sheet in which you need to hide/unhide rows, changing the sheet name, column, and rows to suit.
3) In a standard module put the second lot of code which is a modified version of Johnny's code,

Private Sub Worksheet_Change(ByVal Target As Range)

'
    Application.ScreenUpdating = False                                                                              ' Turn ScreenUpdating off
    Application.Calculation = xlManual                                                                              ' Turn off Calculations
    Application.EnableEvents = False                                                                                ' Turn EnableEvents off
                                                                              
    If Not Intersect(Target, Range("E13")) Is Nothing Then
        ' XXX Add a Call line for each sheet needing the Row Hide/Unhide applied and change parameters to suit
        Call HideShowRows(shtName:="Sheet2", ColumnToTest:="A", StartRowToTest:=11, _
                            LastRowToTest:=110)
        Call HideShowRows(shtName:="Sheet3", ColumnToTest:="A", StartRowToTest:=11, _
                            LastRowToTest:=110)
    End If

'
    Application.EnableEvents = True                                                                                 ' Turn EnableEvents back on
    Application.Calculation = xlAutomatic                                                                           ' Turn Calculations back on
    Application.ScreenUpdating = True                                                                               ' Turn ScreenUpdating back on

End Sub

3) for the standard module called by worksheet event.

Sub HideShowRows(shtName As String, ColumnToTest As String, StartRowToTest As Long, _
                    LastRowToTest As Long)
'
    Application.ScreenUpdating = False                                                                              ' Turn ScreenUpdating off
    Application.Calculation = xlManual                                                                              ' Turn off Calculations
    Application.EnableEvents = False                                                                                ' Turn EnableEvents off
'
    Dim ArrayRow        As Long
    Dim rnghide         As Range, rngshow       As Range
    Dim ColumnArray     As Variant
    Dim sht             As Worksheet
'
    Set sht = Worksheets(shtName)
'
    Set rnghide = Nothing
    Set rngshow = Nothing
'
    ColumnArray = sht.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 = sht.Rows(ArrayRow) Else Set rnghide = Union(rnghide, sht.Rows(ArrayRow))   '       Save row # to rnghide
      Else                                                                                                          '   Else
        If rngshow Is Nothing Then Set rngshow = sht.Rows(ArrayRow) Else Set rngshow = Union(rngshow, sht.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

sorry for asking but can you define code module and standard module? is it sheet code and module code?

 

[Alex Blakenburg]

It is the "code module" of the sheet, which you normally get to by double clicking on the sheet name in the VBA Project window in the left side navigation pane. This is where you put your Event Macros.

Standard module comes under the heading "Modules" in the left side navigation pane.

I should probably adopt your wording, it seems to better align with the more common usage.

 

[pijan11]

It is the "code module" of the sheet, which you normally get to by double clicking on the sheet name in the VBA Project window in the left side navigation pane. This is where you put your Event Macros.

Standard module comes under the heading "Modules" in the left side navigation pane.

I should probably adopt your wording, it seems to better align with the more common usage.

Ok thanks....that's what I thought. I did put the codes in but the rows aren't hidden

 

[Alex Blakenburg]

Show me your sheet code window of the sheet with the E13
The sheet code window of one of the sheets being updated - should have nothing in it.
What the sheet looks like in the sheet being updated showing the sheet name and the column being evaluated for empty cell

 

[pijan11]

Show me your sheet code window of the sheet with the E13
The sheet code window of one of the sheets being updated - should have nothing in it.
What the sheet looks like in the sheet being updated showing the sheet name and the column being evaluated for empty cell

Here is the sheet code window for sheet with cell E13

and the code on that sheet is:

Private Sub Worksheet_Change(ByVal Target As Range)

'
    Application.ScreenUpdating = False                                                                              ' Turn ScreenUpdating off
    Application.Calculation = xlManual                                                                              ' Turn off Calculations
    Application.EnableEvents = False                                                                                ' Turn EnableEvents off
                                                                               
    If Not Intersect(Target, Range("E13")) Is Nothing Then
        ' XXX Add a Call line for each sheet needing the Row Hide/Unhide applied and change parameters to suit
        Call HideShowRows(shtName:="Ecole", ColumnToTest:="A", StartRowToTest:=11, _
                            LastRowToTest:=110)
        Call HideShowRows(shtName:="SAE", ColumnToTest:="A", StartRowToTest:=11, _
                            LastRowToTest:=110)
    End If

'
    Application.EnableEvents = True                                                                                 ' Turn EnableEvents back on
    Application.Calculation = xlAutomatic                                                                           ' Turn Calculations back on
    Application.ScreenUpdating = True                                                                               ' Turn ScreenUpdating back on

End Sub

There are no other codes on any of the sheets.

I do have these codes in ThisWorkbook but in shouldn't interfere....I think:

Private Sub Workbook_Open()

'Enable changes to worksheet by VBA code, even if protected
Sheets("Ecole").Protect Password:="", UserInterfaceOnly:=True
Sheets("SAE").Protect Password:="", UserInterfaceOnly:=True
Application.DisplayFullScreen = True

End Sub

Private Sub Workbook_Close()

Application.DisplayFullScreen = False

End Sub

And here is what the sheet looks like.....as you can see rows after row 12 (because this is what I did input in cell E13 on the other sheet) shoud be hidden:

 

[Alex Blakenburg]

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