在 Excel VBA 中删除一行 [英] Delete a row in Excel VBA

问题描述

我有一段代码，它从列表中查找项目的 excel 行并从列表中删除项目.我想要的...也是删除 Excel 行.

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.

代码在这里

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

我添加 ws.Range(Rand,1).EntireRow.Delete 的地方是我想删除整行的地方，但我不知道该怎么做.我想要什么......如果它在一个单元格中找到相同的值，就像在我的列表的某些选定项目中一样，能够同时删除 excel 中的整行和列表框中的项目.它可以从列表框中删除该项目，但我也不知道如何删除该行

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

推荐答案

Chris Nielsen 的解决方案很简单，而且效果很好.一个稍短的选项是...

Chris Nielsen's solution is simple and will work well. A slightly shorter option would be...

ws.Rows(Rand).Delete

...请注意，删除行时无需指定 Shift，因为根据定义，不可能向左移动

...note there is no need to specify a Shift when deleting a row as, by definition, it's not possible to shift left

顺便说一句，我删除行的首选方法是使用...

Incidentally, my preferred method for deleting rows is to use...

ws.Rows(Rand) = ""

...在初始循环中.然后我使用 Sort 函数将这些行推到数据的底部.这样做的主要原因是删除单行可能是一个非常慢的过程(如果要删除 >100).根据罗伯特·伊尔布林克 (Robert Ilbrink) 的评论，它还可以确保不会遗漏任何内容

...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

您可以通过录制宏并减少代码来学习排序代码，如本Excel专家所示视频.我怀疑最简洁的方法 (Range("A1:Z10").Sort Key1:=Range("A1"), Order1:=xlSortAscending/Descending, Header:=xlYes/No) 只能在预2007 版本的 Excel...但你总是可以减少 2007/2010 等效代码

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

还有几个点...如果您的列表尚未按列排序并且您希望保留顺序，则可以在循环时将行号Rand"粘贴在每行右侧的备用列中.然后，您将按该评论进行排序并将其删除

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

如果您的数据行包含格式，您可能希望找到新数据范围的末尾并删除您之前清除的行.那是为了减小文件大小.请注意，程序末尾的单个大删除不会像删除单行那样损害代码的性能

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

这篇关于在 Excel VBA 中删除一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！