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
'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