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:
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