How to merge blank cells in one column in Office Excel?

Private Sub CommandButton5_Click()
    
'Merge Blank Cell
    Dim i, j, iRowscount, iMgStart, iMgEnd As Integer
    
Dim cX, cX1, startCell, endCell, endCellPrev, strRange As String 'column need merged
    Dim bFlag As Boolean
    
    iRowscount 
= ActiveSheet.UsedRange.Rows.Count
    startCell 
= UCase(Trim(txtColumnName.Text)) & "1"
    bFlag 
= False
    
    
For i = 2 To iRowscount
        cX1 
= UCase(Trim(txtColumnName.Text)) & Trim(Str(i - 1))
        cX 
= UCase(Trim(txtColumnName.Text)) & Trim(Str(i))
        
If bFlag = True Then
            startCell 
= cX1
            bFlag 
= False
        
End If
        Range(cX).Select
        
If Range(cX).Cells.Value <> "" Then
            endCell 
= cX
            endCellPrev 
= cX1
        
Else
            endCell 
= cX
            endCellPrev 
= cX1
        
End If
        
If Range(endCell).Cells.Value <> "" Then
            
'strRange = """" & startCell & ":" & endCellPrev & """"
            strRange = startCell & ":" & endCellPrev
            Debug.Print strRange
            Range(strRange).Select
            
With Selection
                .HorizontalAlignment 
= xlCenter
                .VerticalAlignment 
= xlTop
                .WrapText 
= True
                .Orientation 
= 0
                .AddIndent 
= False
                .IndentLevel 
= 0
                .ShrinkToFit 
= False
                .ReadingOrder 
= xlContext
                .MergeCells 
= False
            
End With
            Selection.Merge
            bFlag 
= True
        
End If
    
Next
    
End Sub
posted @ 2007-08-07 11:00  RayG  阅读(450)  评论(0编辑  收藏  举报