关闭工作簿时删除范围，xls vba
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6422693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
deleting range when closing workbook, xls vba
提问by Kay
i'd like the range
我想要这个范围
Range("A2:G" & z)
to be deleted with closing the workbook - can someone please help my with the code?
在关闭工作簿时被删除 - 有人可以帮助我使用代码吗？
thanks, kay
谢谢，凯
this is what i tried:
这是我试过的：
Option Explicit
Sub Makro1()
'insert clipboard
Workbooks("Pfl_SchutzStat.xls").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'insert formulas to look up sheet ZTAXLIST
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,1)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,3)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,4)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,5)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,6)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,7)"
'autofill formulas
Dim z As Integer
z = Range("A2").End(xlDown).Row
Range("B2:G2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B2:G" & z)
Range("A1").Select
End Sub
here i added the sub that should delete the range i also tried to make it as to close the workbook without saving and without asking for it i tried to insert a msgbox - when closing it seems the macro is not called!
在这里，我添加了应该删除范围的子程序我还尝试关闭工作簿而不保存并且不要求它我尝试插入一个msgbox - 关闭时似乎没有调用宏！
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ev As Boolean
Dim datei As String
Dim z As Integer
z = Range("A2").End(xlDown).Row
datei = ThisWorkbook.Name
ev = Application.EnableEvents
Application.EnableEvents = False
If Workbooks.Count > 1 Then
ActiveWorkbook.Close SaveChanges:=False
Else
Workbooks(datei).Saved = True
Application.Quit
End If
Worksheets("Abfrage").Range("A2:G" & z).ClearContents
Application.EnableEvents = ev
End Sub
回答by JMax
You can use the event vba function BeforeClose: find more information here : http://msdn.microsoft.com/en-us/library/aa220801%28v=office.11%29.aspx
您可以使用事件 vba 函数BeforeClose：在此处找到更多信息：http: //msdn.microsoft.com/en-us/library/aa220801%28v=office.11%29.aspx
Do not forget to point to the worksheet where you want to delete your lines.
不要忘记指向要删除行的工作表。
Here is an example :
这是一个例子：
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'delete the lines of the "Sheet1"
Dim z As Integer
z = 2 'or whatever depending on your previous code
'replace "Sheet1" by the name of your sheet
Worksheets("Sheet1").Range("A2:G" & z).Delete Shift:=xlUp
End Sub
-=EDIT=-
-=编辑=-
First, you don't need to select every cell before setting a formula. For instance you could do :
首先，您不需要在设置公式之前选择每个单元格。例如你可以这样做：
Range("D2").FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,4)"
Instead of :
代替 ：
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,4)"
Besides, did you put the Private Sub Workbook_BeforeClose(Cancel As Boolean) in the ThisWorkbook part in the VBA Editor ? It shouldn't be in a Module or a worksheet module !
此外，您是否将 Private Sub Workbook_BeforeClose(Cancel As Boolean) 放在 VBA 编辑器的 ThisWorkbook 部分？它不应该在模块或工作表模块中！
Regards,
问候，
Max
最大限度
