Jul 15 2022 08:19 AM - edited Jul 15 2022 08:24 AM
I have created four macros on sheet 1. When pressing on any macro, the process has run slowly. I do not know the reason. is there any problem with the code? Is there a faster way to do a "loop" or "if" operations or a better way to write this code? I have attached the code for these macros.
Regards
Sub Page1()
Dim x As Integer
Dim Y As String
Y = Range("B12")
If Y = "" Then
Sheets(1).Range("1:50, 151:" & Rows.Count).EntireRow.Hidden = True
MsgBox "No student's names are included on this page, please select other pages", vbExclamation
Exit Sub
End If
Sheets(1).Rows.Hidden = False
Sheets(1).Range("51:100,151:" & Rows.Count).EntireRow.Hidden = True 'Deleting the page 2
Sheets(1).Range("101:150,151:" & Rows.Count).EntireRow.Hidden = True 'Deleting the page 3
x = WorksheetFunction.Match("D", Range("S:S"), 0)
For r = 1 To 147
If Sheets(1).Range("S" & r).Value = "D" Then
Sheets(1).Range("S" & r).EntireRow.Hidden = True
End If
Next
End Sub
Sub Page2()
Dim x As Integer
Dim Y As String
'Deleting the blank cells
Sheets(1).Rows.Hidden = False
Y = Range("B62")
If Y = "" Then
Sheets(1).Range("51:100, 150:" & Rows.Count).EntireRow.Hidden = True
MsgBox "No student's names are included on this page, please select other pages", vbExclamation
Exit Sub
End If
x = WorksheetFunction.Match("D", Range("S:S"), 0)
Sheets(1).Range("1:50,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the page 1
Sheets(1).Range("101:150,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the page 3
For r = 1 To 150
If Sheets(1).Range("S" & r).Value = "D" Then
Sheets(1).Range("S" & r).EntireRow.Hidden = True
End If
Next
End Sub
Sub Page3()
Sheets(1).Rows.Hidden = False
Dim x As Integer
Dim Y As String
'Deleting the blank cells
Sheets(1).Rows.Hidden = False
Y = Range("B112")
If Y = "" Then
Sheets(1).Range("101:150, 150:" & Rows.Count).EntireRow.Hidden = True
MsgBox "No student's names are included on this page, please select other pages", vbExclamation
Exit Sub
End If
x = WorksheetFunction.Match("D", Range("S:S"), 0)
Sheets(1).Range("1:50,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the page 1
Sheets(1).Range("51:100,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the page 2
For r = 1 To 150
If Sheets(1).Range("S" & r).Value = "D" Then
Sheets(1).Range("S" & r).EntireRow.Hidden = True
End If
Next
End Sub
Sub AllPages()
Dim r As Integer
Dim x, u As Integer
'Deleting the blank cells
Sheets(1).Rows.Hidden = False
x = WorksheetFunction.Match("D", Range("S:S"), 0)
If x >= 12 And x <= 46 Then
Sheets(1).Range("51:150,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the page 1 and 2
End If
If x >= 62 And x <= 96 Then
Sheets(1).Range("47:50,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the % of page 1
Sheets(1).Range("101:111,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the header page 3
Sheets(1).Range("147:150,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the % of page 3
End If
If x >= 112 And x <= 146 Then
Sheets(1).Range("47:50,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the % of page 1
Sheets(1).Range("97:100,150:" & Rows.Count).EntireRow.Hidden = True 'Deleting the % of page 3
End If
For r = 1 To 150
If Sheets(1).Range("S" & r).Value = "D" Then
Sheets(1).Range("S" & r).EntireRow.Hidden = True
End If
Next
End Sub
Jul 15 2022 08:29 AM
Jul 15 2022 08:40 AM
https://techcommunity.microsoft.com/t5/excel/help-with-macro-hygiene-please/m-p/3568358#M153475
Maybe it helps, if not please ignore.
Jul 15 2022 09:12 AM