title: deleting range when closing workbook, xls vba
body: <p>i'd like the range</p>
  
  <pre><code>Range("A2:G" &amp; z)
  </code></pre>
  
  <p>to be deleted with closing the workbook - can someone please help my with the code?</p>
  
  <p>thanks,
  kay</p>
  
  <p>this is what i tried:</p>
  
  <pre><code>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" &amp; z)
      Range("A1").Select
  End Sub
  </code></pre>
  
  <p>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!</p>
  
  <pre><code>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 &gt; 1 Then
          ActiveWorkbook.Close SaveChanges:=False
      Else
          Workbooks(datei).Saved = True
      Application.Quit
      End If
      Worksheets("Abfrage").Range("A2:G" &amp; z).ClearContents
      Application.EnableEvents = ev
  End Sub
  </code></pre>