Excel VBA：查找空单元格并删除该行-Java 学习之路

我有垂直数据，B列中有间隙：G

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

通过B栏筛选
找到一个空单元格
删除空单元格的整行
重复此操作直到找到10个空单元格（这是最棘手的，因为它不应该删除这10个空单元格）// 10只是一个任意数字，没有更多的数据
然后转到C列重复整个过程，依此类推，直到筛选完所有列

我有一些基本的VBA知识，这是我到目前为止在这个主题上找到的代码，但是如何处理这个问题在我脑子里是一团糟 .

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

下面的代码查找B列中的下一个空单元格并选择它 .

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

1 回答

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是您的起始列

回复于 2024-04-20T12:43:05+08:00

Excel VBA：查找空单元格并删除该行

1 回答

相关问题