Excel VBA:查找空单元格并删除行 [英] Excel VBA: Find empty cell and delete the row

问题描述

我在B:G列中有空白的垂直数据

I have vertical data with gaps in columns B:G

我希望我的代码执行以下操作:

I wish that my code does the following:

1. 通过B列的屏幕
2. 找到一个空单元格
3. 删除空单元格的整行
4. 重复此操作，直到找到10个空单元为止(这是最棘手的，因为它不应删除这10个空单元)//10只是一个任意数字，没有更多数据
5. 然后转到C列，重复整个过程，依此类推，直到所有列都被筛选为止

我有一些基本的VBA知识，这是到目前为止我在该主题上找到的代码，但是如何实现这一目标却使我很头疼.

I have some basic VBA knowledge and this is the code I have found on the subject so far, however it's a mess in my head how to approach this.

我遇到的主要问题是代码如何知道何时停止删除并移至下一列.

The main issue I am having is how would the code know when to stop deleting and move to the next column.

下面的代码在B列中查找下一个空单元格并将其选中.

This code below finds the next empty cell in column B and selects it.

Public Sub SelectFirstBlankCell()
Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String

sourceCol = 6   'column F has a value of 6
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

'for every row, find the first blank cell and select it
For currentRow = 1 To rowCount
    currentRowValue = Cells(currentRow, sourceCol).Value
    If IsEmpty(currentRowValue) Or currentRowValue = "" Then
        Cells(currentRow, sourceCol).Select
        Exit For 'This is missing...
    End If
Next
End Sub

推荐答案

Public Sub SelectFirstBlankCell()
Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String

For i = 0 To Cells(1, Columns.Count).End(xlToLeft).Column 'count and loop cols

    sourceCol = 1 + i 'increment with i, move 1 to start column
    rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

    For currentRow = rowCount To 1 Step -1 ' this will start at end and work its way up

        currentRowValue = Cells(currentRow, sourceCol).Value

        If IsEmpty(currentRowValue) Or currentRowValue = "" Or Trim(currentRowValue) = "" Then 'Trim accounts for " " may not be needed
            Cells(currentRow, sourceCol).EntireRow.Delete
        End If

    Next
Next
End Sub

尝试以上.sourceCol = 1 +我可以更改为sourceCol = x + i，其中x是您的起始列

Try above. sourceCol = 1 + i can be changed to sourceCol = x + i where x is your start column

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