关闭工作簿时删除范围，xls vba [英] deleting range when closing workbook, xls vba

问题描述

我想要的范围

 范围（A2：G& z）
  

要关闭工作簿才能删除 - 有人可以帮助我的代码？

谢谢，
kay

这是我试过的：

  Option Explicit 
 Sub Makro1（）
'插入剪贴板
工作簿（Pfl_SchutzStat.xls）。激活
范围（A2）。 
 Selection.PasteSpecial粘贴：= xlPasteValues，操作：= xlNone，SkipBlanks _ 
：= False，Transpose：= False 
'插入公式查找表ZTAXLIST 
范围（ B2）选择
 ActiveCell.FormulaR1C1 == VLOOKUP（RC1，ZTAXLIST！C2：C9,1）
范围（C2）选择
 ActiveCell.FormulaR1C1 ==选择
 ActiveCell.FormulaR1C1 == VLOOKUP（RC1，ZTAXLIST！C2：C9,4）$ b（RC1，ZTAXLIST！C2：C9,3）
 Range（D2 $ b Range（E2）。选择
 ActiveCell.FormulaR1C1 == VLOOKUP（RC1，ZTAXLIST！C2：C9,5）
 Ra nge（F2）。选择
 ActiveCell.FormulaR1C1 == VLOOKUP（RC1，ZTAXLIST！C2：C9,6）
范围（G2）选择
 ActiveCell.FormulaR1C1 == VLOOKUP（RC1，ZTAXLIST！C2：C9,7）
'自动填充公式
 Dim z As Integer 
z = Range（A2）。End（xlDown） b $ b范围（B2：G2）。选择
 Application.CutCopyMode = False 
 Selection.AutoFill Destination：= Range（B2：G& z）
范围（A1）。选择
 End Sub 
  

我添加的子应该删除范围
i也试图使它关闭工作簿而不保存，而不要求
i试图插入一个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 
如果Workbooks.Count> 1然后
 ActiveWorkbook.Close SaveChanges：= False 
 Else 
工作簿（datei）.Saved = True 
 Application.Quit 
 End If 
 Worksheets（ Abfrage）。Range（A2：G& z）.ClearContents 
 Application.EnableEvents = ev 
 End Sub 
  

解决方案

您可以使用事件vba函数 BeforeClose ：在这里找到更多信息： http://msdn.microsoft.com/en-us/library/aa220801%28v = office.11​​％29.aspx

不要忘记指向要删除行的工作表。

这里是一个例子：

  Option Explicit 
 
 Private Sub Workbook_BeforeClose （Cancel As Boolean）
'删除Sheet1的行
 Dim z As Integer 
z = 2'或任何取决于您之前的代码
 
'rep花边Sheet1以您的工作表名称
工作表（Sheet1）。范围（A2：G& z）.Delete Shift：= xlUp 
 End Sub 
  

- = EDIT = p>

首先，在设置公式之前，不需要选择每个单元格。例如你可以做：

  Range（D2）。FormulaR1C1 == VLOOKUP（RC1，ZTAXLIST！C2：C9 ，4）
  

而不是：

  Range（D2）。选择
 ActiveCell.FormulaR1C1 == VLOOKUP（RC1，ZTAXLIST！C2：C9,4）
  

此外，您是否在VBA编辑器的ThisWorkbook部分中放置了Private Sub Workbook_BeforeClose（Cancel As Boolean）它不应该在模块或工作表模块中！

关于

最大

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!

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

解决方案

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

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 !

Regards,

Max

这篇关于关闭工作簿时删除范围，xls vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！