Excel VBA:查找空单元格并删除行 [英] Excel VBA: Find empty cell and delete the row
本文介绍了Excel VBA:查找空单元格并删除行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在B:G列中有空白的垂直数据
I have vertical data with gaps in columns B:G
我希望我的代码执行以下操作:
I wish that my code does the following:
- 通过B列的屏幕
- 找到一个空单元格
- 删除空单元格的整行
- 重复此操作,直到找到10个空单元为止(这是最棘手的,因为它不应删除这10个空单元)//10只是一个任意数字,没有更多数据
- 然后转到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屋!
查看全文