我一直在处理excel文件，以简化从不同非洲农场的采购的过程。

订单已快完成，但我偶然发现了一个令人讨厌的问题。我只剩下一个19MB的文件。

每个工作表有两个活动的X按钮（“ +”和“-”）。总共8个工作表。一周中的每一天+一个在“程序”工作表中收集并累加数量的事件。

按钮“ +”添加指定的行（请参见下面的代码）

Private Sub CommandButton1_Click()

Sheets("FRIDAY").Select activeSheet.Unprotect Password:="ORDER"

Dim varUserInput As Variant  varUserInput = InputBox("Enter Row Number where you want to add a row:",_   "What Row?")  If varUserInput = "" Then Exit Sub

Rows("49:49").Select Selection.Delete Shift:=xlUp

RowNum = varUserInput
Rows(RowNum & ":" & RowNum).Insert Shift:=x1Down
Rows(RowNum + 1 & ":" & RowNum + 1).Copy Range("A" & RowNum)
Range(RowNum & ":" & RowNum).ClearContents
Range("A48:K48").Select

Selection.Copy
Range(RowNum & ":" & RowNum).Select
activeSheet.Paste
activeWindow.SmallScroll Down:=-105
Range("B1:K1").Select
Sheets("FRIDAY").Select activeSheet.Protect Password:="ORDER"

End Sub

按钮“-”删除一行（并添加一行以保持与下面相同的布局：）

Private Sub CommandButton2_Click()

Sheets("FRIDAY").Select activeSheet.Unprotect Password:="ORDER"

Dim varUserInput As Variant  varUserInput = InputBox("Enter Row Number that you want to delete",_   "What Row?")  If varUserInput = "" Then Exit Sub

RowNum = varUserInput
Rows(RowNum & ":" & RowNum).Delete Shift:=x1Down
Rows("49:49").Select Selection.Insert Shift:=xlUp

Range("A48:K48").Select
Selection.Copy
Range("A49:K49").Select
activeSheet.Paste
activeWindow.SmallScroll Down:=-105
Range("B1:K1").Select
Sheets("FRIDAY").Select activeSheet.Protect Password:="ORDER"

End Sub

另外，还有一个宏是myworksheet模块，它使布局保持整洁。

Public Sub HideAllButPrintArea()
Dim xPrintRng As Range
Dim xFirstRng As Range
Dim xLastRng As Range
Application.ScreenUpdating = False

With Application.activeSheet
    .Cells.EntireColumn.Hidden = False
    .Cells.EntireRow.Hidden = False

    If .PageSetup.PrintArea <> "" Then
        Set xPrintRng = .Range(.PageSetup.PrintArea)
    Else
        Set xPrintRng = .UsedRange
    End If

    Set xFirstRng = xPrintRng.Cells(1)
    Set xLastRng = xPrintRng.Cells(xPrintRng.Count)

    If xFirstRng.Row > 1 Then
        .Range(.Cells(1,1),xFirstRng(-0,1)).EntireRow.Hidden = True
    End If

    If xFirstRng.Column > 1 Then
        .Range(.Cells(1,xFirstRng(1,0)).EntireColumn.Hidden = True
    End If

    If xLastRng.Row < .Rows.Count Then
        .Range(xLastRng(2,.Cells(.Rows.Count,1)).EntireRow.Hidden = True
    End If

    If xLastRng.Column < .Columns.Count Then
        .Range(xLastRng(1,2),.Cells(1,.Columns.Count)).EntireColumn.Hidden = 
True
    End If
End With

Application.ScreenUpdating = True

End Sub

如何在每个工作表上保持相同的按钮，同时又将文件大小减小到优选的