Whilst this code works perfectly for what I need, is there a better way to write this VBA code to make it run faster?
VBA Code:
Sub InsertPageBreaks()
Application.ScreenUpdating = False
ActiveWindow.View = xlNormalView
'ActiveSheet.Unprotect Password:="password"
'---------------Set Variables --------
Dim counter As Long
Dim iRange1 As Range
Set iRange1 = Range("B132:B175")
Dim Hide_header1 As Range
Set Hide_header1 = Range("A193:F197")
Dim bdrange1a As Range
Set bdrange1a = Range("A192:F192")
Dim bdrange1b As Range
Set bdrange1b = Range("A193:F193")
'
'-------Check if the blank row count is greater than / equal to 40 rows
If Application.CountBlank(iRange1) >= 40 Then
' If it is, Hide Specified Row Range
Hide_header1.EntireRow.Hidden = True
' Remove Top & Bottom Page Borders To make Pages Look As One
For Each r In bdrange1a
r.Borders(xlEdgeBottom).LineStyle = xlNone
Next r
For Each r In bdrange1b
r.Borders(xlEdgeTop).LineStyle = xlNone
Next r
'------ If blank row count on Page is less than 40, do not hide rows or remove borders
Else
Hide_header1.EntireRow.Hidden = False
End If
'
'-------------- SEARCH FOR MS* LOCATED IN PAGE HEADERS IN COLUMN D & ADD PAGE BREAK ON VISIBLE PAGES ---------------
'
ActiveSheet.ResetAllPageBreaks
Dim fRng As Range, Faddr As String
With ActiveSheet.Range("D2:D" & ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row)
Set fRng = .Cells.Find(What:="*MS*", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not fRng Is Nothing Then
Faddr = fRng.Address
Do
If Not fRng Is Nothing Then
fRng.Offset(0).PageBreak = xlPageBreakManual
End If
Set fRng = .FindNext(fRng)
If fRng Is Nothing Then
Exit Do
End If
If fRng.Address = Faddr Then
Exit Do
End If
Loop
End If
End With
ActiveWindow.View = xlPageBreakPreview
'ActiveSheet.Protect Password:="password"
Application.ScreenUpdating = True
'
MsgBox " PAGE BREAKS SET!!" & vbCrLf & " Press OK"
End Sub
