VBA adding/deleting pages loadtime

skpma

New Member
Joined
Mar 3, 2020
Messages
31
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hello everyone

We have a VBA script for adding and/or deleting pages on a sheet, and move the existing pages accordingly.
Unfortunately, this takes a lot of time (30+ seconds per operation) and I was wondering if there might be any improvement I could do with the existing code?
Below the delete part of the code:

VBA Code:
Public Sub DeletePage(ByVal wPage1 As Integer)

    Dim wp1 As PrintCells, wp2 As PrintCells  
    Dim wr As Integer
    Dim i As Long
    Dim shp As Shape
    Dim rng_shp As Range, rng_pg As Range
    Application.ScreenUpdating = False
   
    If wPage1 < 1 Then Exit Sub
    
    With ActiveSheet
    
        wp1 = GetPageCells(wPage1)
        
        
        Set rng_pg = .Range(.Cells(wp1.sRow, wp1.sCol), .Cells(wp1.eRow, wp1.eCol))
        
        
        For Each shp In .Shapes
           
            Set rng_shp = .Range(shp.TopLeftCell, shp.BottomRightCell)
            
            If Not Intersect(rng_shp, rng_pg) Is Nothing Then
                shp.Delete
            End If
        Next
        
        
        rng_pg.Delete Shift:=xlToLeft
        
        wr = (wPage1 - 1) \ VPAGE + 1
        Do Until wr > .HPageBreaks.Count
            wp2 = GetPageCells(wr * VPAGE + 1)
            Call ChangePage(wr * VPAGE, wr * VPAGE + 1)
            .Range(.Cells(wp2.sRow, wp2.sCol), .Cells(wp2.eRow, wp2.eCol)).Delete Shift:=xlToLeft
            wr = wr + 1
            Application.CutCopyMode = False
        Loop
        DoEvents
    End With
    Set shp = Nothing
    Set rng_shp = Nothing
    Set rng_pg = Nothing
    Application.ScreenUpdating = True
  End Sub

VBA Code:
Private Function GetPageCells(ByVal wPage As Integer) As PrintCells
    Dim w1 As Integer
    Dim w2 As Integer
   
    Application.ScreenUpdating = False
  
    If wPage Mod VPAGE = 0 Then
        w1 = Int(wPage / VPAGE) - 1
        w2 = VPAGE
    Else
        w1 = Int(wPage / VPAGE)
        w2 = wPage Mod VPAGE
    End If
    With GetPageCells
        .sRow = w1 * P_ROW + 1
        .eRow = (w1 + 1) * P_ROW
        .sCol = (w2 - 1) * P_COL + 1
        .eCol = w2 * P_COL
       
    End With
Application.ScreenUpdating = True
End Function

VBA Code:
Public Sub ChangePage(ByVal wPage1 As Integer, ByVal wPage2 As Integer)

    Dim wp1 As PrintCells, wp2 As PrintCells
    
    
    
    Application.ScreenUpdating = False
    
    If wPage1 = wPage2 Or wPage1 < 1 Or wPage2 < 1 Then Exit Sub
    
    With ActiveSheet
       
        wp1 = GetPageCells(wPage1)
        wp2 = GetPageCells(wPage2)
        
       
        .Range(.Cells(wp1.sRow, wp1.sCol), .Cells(wp1.eRow, wp1.eCol)).Cut .Range(.Cells(W_ROW, W_COL).Address)
       
        .Range(.Cells(wp2.sRow, wp2.sCol), .Cells(wp2.eRow, wp2.eCol)).Cut .Range(.Cells(wp1.sRow, wp1.sCol), .Cells(wp1.eRow, wp1.eCol))
       
        .Range(.Cells(W_ROW, W_COL), Cells(W_ROW + P_ROW, P_COL)).Cut .Range(.Cells(wp2.sRow, wp2.sCol), .Cells(wp2.eRow, wp2.eCol))
        DoEvents
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
   
End Sub

VBA Code:
Private Const P_COL = 12   
Private Const P_ROW = 63    
Private Const VPAGE = 20    
'Private Const HPAGE = 5   
Private Const W_COL = 1     
Private Const W_ROW = 631   
Private Const PICSCL = 0.99
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,214,919
Messages
6,122,260
Members
449,075
Latest member
staticfluids

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
Back
Top