vba 使用excel VBA清除除第一行之外的整行数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45993834/
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
Clear entire row data except the first row using excel VBA
提问by acr
I am using below code to clear contents from A2:H2 rows except the first one where I have the header. This code works well if there are no blank cells in between. But how can I clear everything eventhough there is a blank cell?
我正在使用下面的代码来清除 A2:H2 行中的内容，除了我有标题的第一个行。如果中间没有空白单元格，则此代码运行良好。但是，即使有一个空白单元格，我如何清除所有内容？
Sub Clear()
Dim s1Sheet As Worksheet
Set s1Sheet = Workbooks("StockScreen.xlsm").Sheets("TimeStampWork")
s1Sheet.Range(s1Sheet.Range("A2:H2"), s1Sheet.Range("A2:H2").End(xlDown)).ClearContents
End Sub
采纳答案by Mrig
Instead of
代替
s1Sheet.Range(s1Sheet.Range("A2:H2"), s1Sheet.Range("A2:H2").End(xlDown)).ClearContents
try
尝试
s1Sheet.Range("A2:H" & s1Sheet.Cells(s1Sheet.Rows.Count, "A").End(xlUp).Row).
or
或者
With s1Sheett
.Range("A2:H" & .Cells(.Rows.Count, "A").End(xlUp).Row).ClearContents
End With
here last row with data is counted using Column A.
这里有数据的最后一行使用Column A.
回答by bluetata
Below is my sample code:
下面是我的示例代码：
Sub ClearContentExceptFirst()
Rows("2:" & Rows.Count).ClearContents
End Sub
回答by Jurjen
If you simply want to delete the entire row from a specific row number to a specific row number, you can avoid using the Rangeand Cellproperties, but instead you can use the Rowsproperty:
如果您只是想删除从特定行号到特定行号的整行，则可以避免使用Range和Cell属性，而是可以使用该Rows属性：
s1Sheett.Rows("2:10").ClearContents
Or if you want to delete the everything from a specific row number to the last row with data, you can use:
或者，如果您想删除从特定行号到带有数据的最后一行的所有内容，您可以使用：
s1Sheett.Rows("2:" & currentSheet.Rows.Count).ClearContents
回答by Hari Das
This is what works for me
这对我有用
Sheets("MY_SHEET_NAME").Rows("2:" & Sheets("MY_SHEET_NAME").Rows.Count).ClearContents
