Merge cells but skip empty cells

Question

I am trying to merge cells in multiple columns and skip the cells which are empty. I am able to merge and align the values in the cells to center if they are not empty. However, if cells are empty the script does not throw any error but does nothing at all.

Here is the part of my script that merges and aligns:

Sub MergeAndCenterCells()

Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")
Dim varTestVal As Variant
Dim intRowCount As Integer
Dim intAdjustment As Integer


ws.Range("A3").Select

While Selection.Offset(1, 0).Value <> ""
    intRowCount = 1
    varTestVal = Selection.Value
    While Selection.Offset(1, 0).Value = varTestVal
        intRowCount = intRowCount + 1
        Selection.Offset(1, 0).Select
        Selection.ClearContents
    Wend
    intAdjustment = (intRowCount * -1) + 1
    Selection.Offset(intAdjustment, 0).Select
    Selection.Resize(intRowCount, 1).Select
    With Selection
        .Merge
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    Selection.Offset(1, 0).Resize(1, 1).Select
Wend
End Sub

How can I make it work for cells in Column A that are empty? Thanks

Once you reach an empty cell, your `While` loop will end. First, I highly suggest [avoiding the use of `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), and get a loop through a range going. Then you can check the cell status (empty or not) and go from there. — BruceWayne, Apr 17 '18 at 16:09
Set a filter in column A so empty cells are not displayed. Then you can do the merge and align on the cells — Zac, Apr 17 '18 at 16:11
@Zac I cannot use the filter since I need the blank columns as they are. I just want to work around my script so it just skips the empty cells — PowerToYou, Apr 17 '18 at 17:03
Is there any way I can manipulate this script to achieve it? — PowerToYou, Apr 17 '18 at 17:59

Merge cells but skip empty cells

0 Answers0