vba 错误 1004 Range 类的删除方法失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21709024/
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
Error 1004 Delete method of Range class failed
提问by Hycinth
When I start a new secession, I want to delete old data between specific sheets. (Between Green & Red) . Unfortunately I get this error message and can't figure out what I do wrong.
当我开始新的分离时，我想删除特定工作表之间的旧数据。（介于绿色和红色之间）。不幸的是，我收到此错误消息，无法弄清楚我做错了什么。
“Error 1004 Delete method of Range class failed “
“错误 1004 Range 类的删除方法失败”
Please Help ! Thanks.
请帮忙 ！谢谢。
'-----------------------------
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
回答by Hycinth
This Works now. I just have to include:
* If statement to check the lRow value is > 2
* Increase the Range cell value from 2 -> 3 ( Set Rng = .Range(.Cells(3,1)...)
这现在有效。我只需要包括：
* If 语句检查 lRow 值是 > 2
* 增加范围单元格值从 2 - > 3 （ Set Rng = .Range(.Cells(3,1)...）
'-----------------------------
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
If lRow > 2 Then
Set Rng = .Range(.Cells(3, 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
End If
Application.DisplayAlerts = True ' Back to normal
End With
Next J
End Sub
