vba 如何在excel vba中使用范围函数删除特定行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20812111/
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
How to delete specific rows using range function in excel vba
提问by tahir mehmood
I want to delete all rows in excel sheet if specific column value starts with 1.
如果特定列值以 1 开头，我想删除 Excel 工作表中的所有行。
For example, if range of A1:Ahaving values starts with 1 then I want to delete all those rows using excel vba.
例如，如果A1:A值的范围从 1 开始，那么我想使用 excel vba 删除所有这些行。
How to get it?
如何获得？
Dim c As Range
Dim SrchRng
Set SrchRng = Sheets("Output").UsedRange
Do
Set c = SrchRng.Find("For Men", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
回答by MarkHoward02
Dim Value As String
Dim CellName As String
Dim RowNumber As Long
Do While Value <> ""
CellName = "A" + RowNumber
Value = ActiveSheet.Cells(GetRowNumber(CellName), GetColumnNumber(CellName)).Value
If Mid(Value, 1, 1) = "2" Then
ActiveSheet.Range("A" & RowNumber).EntireRow.Delete
End If
RowNumber = RowNumber + 1
Loop
Private Function GetColumnNumber(ByVal CellName As String) As Long
For L = 1 To 26
If Left(CellName, 1) = Chr(L + 64) Then
GetColumnNumber = L
Exit For
End If
Next
End Function
Private Function GetRowNumber(ByVal CellName As String) As Long
GetRowNumber = CLng(Mid(CellName, 2))
End Function
回答by MarkHoward02
You may be pushing the bounds of what is reasonable to do in Excel vba.
您可能正在推动 Excel vba 中合理操作的界限。
Consider importing the Excel file into Microsoft Access.
考虑将 Excel 文件导入 Microsoft Access。
Then, you can write 2 Delete Queries and they will run uber fast:
然后，您可以编写 2 个删除查询，它们将运行得非常快：
DELETE FROM MyTable WHERE col1 like '2*'
DELETE FROM MyTable WHERE col2 LIKE '*for men*' OR col3 LIKE '*for men*'
After deleting those records, you can export the data to a new Excel file.
删除这些记录后，您可以将数据导出到新的 Excel 文件中。
Also, you can write an Access Macro to import the Excel File, run the Delete Queries, and Export the data back to Excel.
此外，您可以编写一个 Access 宏来导入 Excel 文件、运行删除查询并将数据导出回 Excel。
And you can do all of this without writing a line of VBA Code.
您无需编写一行 VBA 代码即可完成所有这些工作。
回答by Absinthe
Here's the required code with comments on how it works. Feed the worksheet and column number to the sub and call it e.g. Delete Rows 2, Sheets("myWorksheet"):
这是必需的代码，并附有关于它如何工作的注释。将工作表和列号提供给 sub 并调用它，例如 Delete Rows 2, Sheets("myWorksheet")：
Sub DeleteRows(columnNumber as Integer, ws as WorkSheet)
Dim x as long, lastRow as Long
' get the last used row
lastRow = ws.cells(1000000, columnNumber).end(xlUp).Row
'loop backwards from the last row and delete applicable rows
For x = lastRow to 1 Step -1
' if the cell starts with a number...
If IsNumeric(Left(ws.Cells(x, columnNumber), 1) Then
'Delete it the row if it's equaal to 1
If Left(ws.Cells(x, columnNumber), 1) = 1 Then ws.Rows(x &":"& x).Delete
End If
Next x
End Sub
回答by Jorge Novo
You can try:
你可以试试：
Sub delete()
tamano = Range("J2") ' Value into J2
ifrom = 7 ' where you want to delete
'Borramos las celdas
'Delete column A , B and C
For i = ifrom To tamano
Range("A" & i).Value = ""
Range("B" & i).Value = ""
Range("C" & i).Value = ""
Next i
End Sub