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

问题描述

我有一段代码从列表中找到一个项目的excel行，并从列表中删除项目。我想要的是删除Excel行。

代码在这里

  Private Sub imperecheaza_Click（）
 Dim ws As Worksheet 
 Dim Rand As Long 
设置ws =工作表（BD_IR）
 Rand = 3 
 Do While ws.Cells（Rand，4）.Value<> 而兰德65000 
如果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'（这里我想删除符合条件的整个行从If语句）
 gksluri.RemoveItem gksluri.ListIndex 
退出Do 
结束如果
 Rand = Rand + 1 
循环
 End Sub 
  

我在哪里添加了ws.Range（Rand，1）.EntireRow.Delete是我想要删除整行的地方，但我不知道该怎么做。我想要的...如果它在单元格中找到与我的列表中的某些选定项目相同的值，以便能够删除excel中的整个行和列表框中的项目。它可以从列表框中删除该项目，但是我不知道如何删除该行。

解决方案

Chris Nielsen的解决方案很简单，工作顺利。稍微较短的选项将是...

  ws.Rows（Rand）。删除
  

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

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

  ws.Rows（Rand）=
  

...在初始循环中。然后我使用Sort函数将这些行推到数据的底部。主要原因是因为删除单个行可能是一个非常慢的过程（如果您删除> 100）。它还可以确保根据Robert Ilbrink的评论没有任何错过

您可以通过记录宏并减少代码来学习排序代码，如expert Excel视频。我有一个怀疑，最简单的方法（Range（A1：Z10）。Sort Key1：= Range（A1），Order1：= xlSortAscending / Descending，Header：= xlYes / No） 2007版本的Excel ...但是您可以随时减少2007/2010等价代码

更多点...如果您的列表还没有按列排序，您希望保留订单，您可以在循环中将行号'Rand'放在每行右侧的备用列中。然后，您可以按照该注释进行排序并将其删除

如果您的数据行包含格式，则可能希望找到新数据范围的结尾，并删除您的行早点清除这是为了保持文件大小。请注意，在过程结束时，单个大的删除将不会以删除单个行的方式

而损害代码的性能

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.

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

解决方案

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

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

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

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

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屋！