G
Guest
Guest
My spreadsheet has a number section that I have built macros to add additional number section for the user if they need more space, and also to delete sections if they added a section but later dont need it. The problem is that if a section is added but then deleted, the file almost doubles in size. I was testing this by adding and deleting a section a few times and the file grew from 412k to 33meg in a short time.
Here is my code any help would be greatly appreciated.
Public Sub AddNum()
On Error GoTo err_AddNum
ActiveSheet.Unprotect ("2468")
Range(Cells(Rows.Count, 3).Address).End(xlUp).Select
[np1].Copy ActiveCell.Offset(2, -2)
ActiveSheet.PageSetup.PrintArea = Parea()
ActiveSheet.Protect ("2468")
Exit Sub
err_AddNum:
ActiveSheet.Protect ("2468")
MsgBox (Err.Description)
End Sub
Public Sub DelNum()
ActiveSheet.Unprotect ("2468")
If Range(Cells(Rows.Count, 3).Address).End(xlUp).Row < 200 Then
MsgBox ("Cannot Delete Last Number Sheet")
Else
Range(Cells(Rows.Count, 3).Address).End(xlUp).Offset(-41, 0).Rows("1:43").EntireRow.Delete Shift:=xlUp
End If
Range(Cells(Rows.Count, 3).Address).End(xlUp).Select
ActiveSheet.PageSetup.PrintArea = Parea()
err_DelNum:
ActiveSheet.Protect ("2468")
End Sub
Here is my code any help would be greatly appreciated.
Public Sub AddNum()
On Error GoTo err_AddNum
ActiveSheet.Unprotect ("2468")
Range(Cells(Rows.Count, 3).Address).End(xlUp).Select
[np1].Copy ActiveCell.Offset(2, -2)
ActiveSheet.PageSetup.PrintArea = Parea()
ActiveSheet.Protect ("2468")
Exit Sub
err_AddNum:
ActiveSheet.Protect ("2468")
MsgBox (Err.Description)
End Sub
Public Sub DelNum()
ActiveSheet.Unprotect ("2468")
If Range(Cells(Rows.Count, 3).Address).End(xlUp).Row < 200 Then
MsgBox ("Cannot Delete Last Number Sheet")
Else
Range(Cells(Rows.Count, 3).Address).End(xlUp).Offset(-41, 0).Rows("1:43").EntireRow.Delete Shift:=xlUp
End If
Range(Cells(Rows.Count, 3).Address).End(xlUp).Select
ActiveSheet.PageSetup.PrintArea = Parea()
err_DelNum:
ActiveSheet.Protect ("2468")
End Sub