有没有办法排除行中的空白单元格

Question

我有Excel工作表，行中有空白单元格。但我希望行中没有空白单元格。

前任。

James|23-jul-18|24-jul-18|25-jul-18|      |       |      |03-aug-18

自

James|23-jul-18|24-jul-18|25-jul-18 |03-aug-

提前致谢

Answer 1

对于一个宏，它会自动为您执行此操作，并且除了作为公式结果的空白之外，还会捕获实际上为空白的空白：

Sub DeleteBlanks()
Dim rSearch As Range
Dim rFound As Range
Dim rDel As Range
Dim sFirst As String
Dim LastRow As Long, LastCol As Long
'Get search range, have user select rows
On Error Resume Next
Set rSearch = Application.InputBox("Select the rows to delete blanks", "Delete Blank Range Selection", Selection.Address, Type:=8)
On Error GoTo 0
If rSearch Is Nothing Then Exit Sub 'Pressed cancel
'Limit search range to only populated area
Set rFound = rSearch.Find("*", rSearch.Cells(1), xlValues, xlPart, xlByRows, xlPrevious)
If Not rFound Is Nothing Then LastRow = rFound.Row Else LastRow = rSearch.Row + rSearch.Rows.Count - 1
Set rFound = rSearch.Find("*", rSearch.Cells(1), xlValues, xlPart, xlByColumns, xlPrevious)
If Not rFound Is Nothing Then LastCol = rFound.Column Else LastCol = rSearch.Column + rSearch.Columns.Count - 1
Set rSearch = rSearch.Parent.Range(rSearch.Cells(1), rSearch.Parent.Cells(LastRow, LastCol))
'Search for blanks
Set rFound = rSearch.Find("", rSearch.Cells(rSearch.Cells.Count), xlValues, xlWhole, , xlNext)
If Not rFound Is Nothing Then
sFirst = rFound.Address
Do
If Not rDel Is Nothing Then Set rDel = Union(rDel, rFound) Else Set rDel = rFound
Set rFound = rSearch.FindNext(rFound)
Loop While sFirst <> rFound.Address
End If
'Delete all blanks found, shifting cells to left
If Not rDel Is Nothing Then rDel.Delete xlShiftToLeft
End Sub

如何使用宏：

1. 创建将在其上运行宏的工作簿的副本
   • 始终在工作簿副本上运行新代码，以防代码运行不流畅
   • 对于任何删除任何内容的代码尤其如此
2. 在复制的工作簿中，按 Alt+F11 打开 Visual Basic 编辑器
3. 插入 |模块
4. 复制提供的代码并粘贴到模块中
5. 关闭 Visual Basic 编辑器
6. 在 Excel 中，按 Alt+F8 以显示要运行的可用宏的列表
7. 双击所需的宏(我将其命名为DeleteBlanks(