title: Error 1004 Delete method of Range class failed
body: <p>When I start a new secession, I want to delete old data between specific sheets. (Between Green &amp; Red) . Unfortunately I get this error message and can’t figure out what I do wrong. </p>
  
  <p>“Error 1004 Delete method of Range class failed “</p>
  
  <p>Please Help !
  Thanks.</p>
  
  <pre><code>'-----------------------------
  Sub Test()
  '-----------------------------
      Dim ws As Worksheet
      Dim lRow As Long, lCol As Long
      Dim Rng As Range
      Dim beginIdx As Integer, endIdx As Integer
  
      '-- Get the 'Green' and 'Red' indexses in the active workbook .
      beginIdx = ActiveWorkbook.Sheets("Green").Index + 1
      endIdx = ActiveWorkbook.Sheets("Red").Index - 1
  
      '-- Delete old data between 'Green' and 'Red' tabs
      For J = beginIdx To endIdx
  
          '-- Set this to the relevant worksheet
          Set ws = ActiveWorkbook.Sheets(J)
  
          With ws
                '-- Get the last row and last column
                lRow = .UsedRange.SpecialCells(xlCellTypeLastCell).row
                lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
  
                '-- Set the  sheet range to delete old data leaving the headings intact
                Set Rng = .Range(.Cells(2, 1), .Cells(lRow, lCol))
  
                Application.DisplayAlerts = False   ' Get rid of pop-up message
  
                With Rng
                      '-- Now delete the old data from the sheet
                      .EntireRow.Delete
                End With
  
                Application.DisplayAlerts = True    ' Back to normal
          End With
  
      Next J
  
  End Sub
  </code></pre>