Question

VB代码如何删除excel中B列中为空值的整行

请给出详细VB代码，如果excel中某行B列为空就删除该行，如何实现，而且有的空行是连续的，比如4、5、6行的B列都为空。感谢高手提供能执行的代码。

Answer 1

Sub 删除B列空格所在的行()
Application.ScreenUpdating = False
Dim a As long, b As long
Dim i As Long
b = Cells(65536, 1).End(xlUp).Row
On Error GoTo 100
For i = 1 To 256
a = Cells(65536, i).End(xlUp).Row
If b >= a Then
b = b
Else
b = a
End If
Next i
For i = b To 1 Step -1
If Cells(i, 2) = "" Then
Range(i & ":" & i).Delete shift:=xlUp
End If
Next i
100:
Application.ScreenUpdating = True
End Sub

Answer 2

三行代码搞掂！

Sub 删除B列为空的所有行()
Columns("B:B").Select '选中B列
Selection.SpecialCells(xlCellTypeBlanks).Select ‘在所选择范围内再次选中空单元格
Selection.EntireRow.Delete ’删除当前所选中单元格的整行
End Sub
----------------
其实只需要一行代码就行了！呵呵，算法与上面三行是一样的。

Sub 删除B列为空的所有行()
Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

有疑问，请Hi我或给我发百度消息
GoodLuck!

Answer 3

Dim xlApp As Excel.Application '定义EXCEL类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类
Public Sub FindMe(strToFind As String)
Dim rngC As Range '对象
Dim FirstAddress As String '起始位置
Dim intS As Integer '保存位置
Dim wSht As Worksheet
'Dim ro As Long
Application.ScreenUpdating = False '关闭刷新
intS = 1
With xlsheet.UsedRange '' 选择表销售汇总表中的使用范围
Set rngC = .Find(what:=strToFind, LookAt:=xlWhole)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.Delete Shift:=xlUp '单元格向上移动
intS = intS + 1
Set rngC = .FindNext() 'findnext方法不需要参数
Loop Until rngC Is Nothing ' And rngC.Address <> FirstAddress
End If
End With

Application.ScreenUpdating = True '开启刷新

End Sub
Private Sub Command1_Click()
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
Set xlBook = xlApp.Workbooks.Open(App.Path & "\销售汇总表.xls") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表中第一个表
xlApp.Visible = False
For I = 2 To xlsheet.UsedRange.Rows.Count
If InStr(xlsheet.Cells(I, 13), "-") > 0 Then FindMe xlsheet.Cells(I, 13)
Next
Application.DisplayAlerts = False
xlBook.Save '保存销售表
xlBook.Close
Application.DisplayAlerts = True
xlApp.Quit '关闭EXCEL
Set xlsbook = Nothing
Set xlsheet = Nothing
Set xlApp = Nothing '释放EXCEL对象
MsgBox "完成!"
End Sub