Microsoft Office Forums - View Single Post

gmayor · 04-22-2015, 10:44 PM

This sort of thing occurs when there is phantom data in cells not used for the merge (maybe a space that is difficult to spot). The following Excel macro should enable you to clean up the worksheet to avoid the problem. Certainly it fixes the problem on the example sheet you provided.

Code:

Option Explicit

Sub CleanSheet()
Dim xlSheet As Object
Dim iCols As Long, iCol As Long, c As Long
Dim iRows As Long, iRow As Long, r As Long
    Set xlSheet = ActiveSheet
    iRows = xlSheet.UsedRange.Rows.Count
    iCols = xlSheet.UsedRange.Columns.Count
    iRow = xlSheet.Cells(xlSheet.Rows.Count, 1).End(-4162).Row
    iCol = xlSheet.Cells(1, xlSheet.Columns.Count).End(-4159).Column
    If iRows > iRow Then
        For r = iRows To iRow + 1 Step -1
            xlSheet.Rows(r).EntireRow.Delete
            DoEvents
        Next r
    End If
    If iCols > iCol Then
        For c = iCols To iCol + 1 Step -1
            xlSheet.Columns(c).EntireColumn.Delete
            DoEvents
        Next c
    End If
    Set xlSheet = Nothing
lbl_Exit:
    Exit Sub
End Sub