Is There A Better / Faster VBA Code To Achieve The Same Outcome?

bearwires

Board Regular
Joined
Mar 25, 2008
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Craggs82

Board Regular
Joined
Jun 28, 2016
Messages
92
Fairly sure you don't need to loop the range to set the border formatting, should save a tiny amount of time
VBA Code:
            For Each r In bdrange1a
                r.Borders(xlEdgeBottom).LineStyle = xlNone
            Next r
           
            For Each r In bdrange1b
                r.Borders(xlEdgeTop).LineStyle = xlNone
            Next r
can become
VBA Code:
            bdrange1a.Borders(xlEdgeBottom).LineStyle = xlNone
            bdrange1b.Borders(xlEdgeTop).LineStyle = xlNone

edit: typo
 

Forum statistics

Threads
1,181,764
Messages
5,931,910
Members
436,808
Latest member
Dalton50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top