在 Excel VBA 中删除一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7851859/
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
Delete a row in Excel VBA
提问by Andrei Ion
I have this piece of code which finds the excel row of an item from a list and deletes the items from a list. What I want... is to delete the Excel row as well.
我有这段代码，它从列表中找到一个项目的 excel 行并从列表中删除这些项目。我想要的......也是删除Excel行。
The code is here
代码在这里
Private Sub imperecheaza_Click()
Dim ws As Worksheet
Dim Rand As Long
Set ws = Worksheets("BD_IR")
Rand = 3
Do While ws.Cells(Rand, 4).Value <> "" And Rand < 65000
If ws.Cells(Rand, 4).Value = gksluri.Value * 1 And ws.Cells(Rand, 5).Value = gksluri.List(gksluri.ListIndex, 1) * 1 Then
ws.Range(Rand, 1).EntireRow.Delete '(here I want to delete the entire row that meets the criteria from the If statement)
gksluri.RemoveItem gksluri.ListIndex
Exit Do
End If
Rand = Rand + 1
Loop
End Sub
Where I added ws.Range(Rand,1).EntireRow.Delete is where I want to delete the entire row but I don't know how to do it. What I want... if it finds the same value in a cell like in some selected item of my list to be able to remove both the entire row in excel and the item from the listbox. It works to remove the item from the listbox but I don't know how to remove the row as well
我添加 ws.Range(Rand,1).EntireRow.Delete 的地方是我想删除整行的地方，但我不知道该怎么做。我想要什么......如果它在一个单元格中找到相同的值，就像在我的列表中的某些选定项目中一样能够删除excel中的整行和列表框中的项目。它可以从列表框中删除该项目，但我也不知道如何删除该行
回答by Ed Bolton
Chris Nielsen's solution is simple and will work well. A slightly shorter option would be...
Chris Nielsen 的解决方案很简单，而且效果很好。一个稍微短一点的选择是......
ws.Rows(Rand).Delete
...note there is no need to specify a Shift when deleting a row as, by definition, it's not possible to shift left
...请注意，删除行时无需指定 Shift，因为根据定义，不可能向左移动
Incidentally, my preferred method for deleting rows is to use...
顺便说一句，我删除行的首选方法是使用...
ws.Rows(Rand) = ""
...in the initial loop. I then use a Sort function to push these rows to the bottom of the data. The main reason for this is because deleting single rows can be a very slow procedure (if you are deleting >100). It also ensures nothing gets missed as per Robert Ilbrink's comment
...在初始循环中。然后我使用 Sort 函数将这些行推到数据的底部。这样做的主要原因是因为删除单行可能是一个非常缓慢的过程（如果您要删除 >100）。根据罗伯特·伊尔布林克 (Robert Ilbrink) 的评论，它还可以确保不会遗漏任何内容
You can learn the code for sorting by recording a macro and reducing the code as demonstrated in this expert Excel video. I have a suspicion that the neatest method (Range("A1:Z10").Sort Key1:=Range("A1"), Order1:=xlSortAscending/Descending, Header:=xlYes/No) can only be discovered on pre-2007 versions of Excel...but you can always reduce the 2007/2010 equivalent code
您可以通过录制宏并减少代码来学习排序代码，如此Excel 专家视频中演示的那样。我怀疑最简洁的方法 (Range("A1:Z10").Sort Key1:=Range("A1"), Order1:=xlSortAscending/Descending, Header:=xlYes/No) 只能在预2007 版本的 Excel...但您始终可以减少 2007/2010 等效代码
Couple more points...if your list is not already sorted by a column and you wish to retain the order, you can stick the row number 'Rand' in a spare column to the right of each row as you loop through. You would then sort by that comment and eliminate it
还有几点...如果您的列表尚未按列排序并且您希望保留顺序，则可以在循环时将行号“Rand”粘贴在每行右侧的备用列中。然后，您将按该评论排序并删除它
If your data rows contain formatting, you may wish to find the end of the new data range and delete the rows that you cleared earlier. That's to keep the file size down. Note that a single large delete at the end of the procedure will not impair your code's performance in the same way that deleting single rows does
如果您的数据行包含格式，您可能希望找到新数据范围的末尾并删除您之前清除的行。那是为了减小文件大小。请注意，程序末尾的单个大删除不会像删除单行那样损害代码的性能
回答by Jordan D. Karim
Better yet, use union to grab all the rows you want to delete, then delete them all at once. The rows need not be continuous.
更好的是，使用 union 获取要删除的所有行，然后一次性删除它们。行不必是连续的。
dim rng as range
dim rDel as range
for each rng in {the range you're searching}
if {Conditions to be met} = true then
if not rng is nothing then
set rDel = union(rng,rDel)
else
set rDel = rng
end if
end if
next
rDel.entirerow.delete
That way you don't have to worry about sorting or things being at the bottom.
这样你就不必担心排序或东西在底部。
回答by chris neilsen
Change your line
改变你的线路
ws.Range(Rand, 1).EntireRow.Delete
to
到
ws.Cells(Rand, 1).EntireRow.Delete
回答by Matt Wilko
Something like this will do it:
像这样的事情会做到：
Rows("12:12").Select
Selection.Delete
So in your code it would look like something like this:
所以在你的代码中它看起来像这样：
Rows(CStr(rand) & ":" & CStr(rand)).Select
Selection.Delete
