VBA没有删除空白单元格 [英] VBA isn't deleting blank cells

问题描述

我一直在使用IF条件来查找电子表格中的空白单元格，然后删除不需要的行部分并将其他单元格向上移动.一切顺利，但是当我使用此数据库时，代码不会删除某些行.

I've been using a IF condition to find the blank cells in the spreadsheet, then delete the part of the line I don't want and move the other cells up. It was going well, but when I used this database the code won't delete some lines.

以下是查找和删除该行部分的以下部分:

Here is the following part where it find and deletes part of the line:

Dim wb As Workbook
Dim Disciplinas As Worksheet
Set wb = ThisWorkbook
Set Disciplinas = wb.Sheets("Disciplinas")

Dim start_time As range
Dim i As Integer, nLines As Integer

nLines = Disciplinas.Cells(Rows.Count, 2).End(xlUp).Row

Set start_time = Disciplinas.Rows(1).Find(what:="HR_INICIO", LookIn:=xlValues, lookat:=xlWhole)

For i = 2 To nLines
    If Disciplinas.Cells(i, start_time.Column) = "" Then
        Disciplinas.Cells(i, 2).Resize(, 13).Delete Shift:=xlUp
    End If
Next

以下是文件:电子表格(单元格我遇到的麻烦是E105到E125.在我第一次运行代码时，其他的已经被删除了.

Here is the file: Spreadsheet (the cells I'm having trouble are E105 to E125. The other ones have already been deleted in the first time I ran the code).

而不是 If Disciplinas.Cells(i，start_time.Column)="然后我尝试了 vbNullString 和 isEmpty()= TRUE ，但所有人都没有删除这些行.我直接在电子表格中使用了 = ISBLANK()公式，结果为true.

Instead of If Disciplinas.Cells(i, start_time.Column) = "" Then i've tried vbNullString and isEmpty() = TRUE, where all of them did not delete the lines. I've used the formula =ISBLANK() directly in the spreadsheet and the result was true.

我已经注意到，如果我多次运行代码，最终它将删除我想要的所有行.我问自己为什么它不能在第一轮中将其全部删除?谢谢.

I have noticed if I run the code multiple times eventualy it deletes all the lines I want. I ask myself why it don't remove it all in the first run? Thanks.

推荐答案

解决方案确实非常简单，只是有必要像paulsm4所建议的那样向后运行循环.

The solution was indeed quite simple, it was just necessary to run the loop backwards, as paulsm4 suggested.

我必须将对于i = 2更改为nLines ，对于对于i = nLines更改为2步骤-1 .

解释是，当VBA删除该行并将其向上移动时，计数器会递增，然后向下移动一行，就像Peh所解释的那样丢失"了一些行.

The explanation is that when VBA deletes the line and moves it up the counter increments and go one line down, "missing" some lines, as Peh explained.

这篇关于VBA没有删除空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！