Loop / Do while macos. Need help

[Evgeny]

I'm new in macros, went through previous posts but can't make mine work.

I have a worksheet (MS office 2007) with table. I'd like to merge cell 1 and 2 in each columns till there is text in cell 1 and 2. Below is example for column A:

Sub Macro7()
'
' Macro7 Macro
'

'
Range("A1:A2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
End Sub

Thanks
Evgeny

 

[Momentman]

Try

Sub mergecells()
    c = 1
    Do
        Range(Cells(1, c), Cells(2, c)).Select
        Selection.mergecells = True
        c = c + 1
    Loop Until Cells(1, c) <> "" And Cells(2, c) <> ""
End Sub

 

[Evgeny]

Try

Sub mergecells()
    c = 1
    Do
        Range(Cells(1, c), Cells(2, c)).Select
        Selection.mergecells = True
        c = c + 1
    Loop Until Cells(1, c) <> "" And Cells(2, c) <> ""
End Sub

nope. merge A1 and A2, but doesn't continue for the rest columns.

 

[Momentman]

I dont get. The macro is to merge rows(1 and 2) of column A and then proceed to rows(1 and 2) of column B and so on till it gets to a column where rows(1 and 2) are not blank.

Is that right?

 

[Evgeny]

Correct, but the macros for you merge only A column.

 

[Momentman]

I just ran it and it merged up to column F 'cos i had empty rows from A to E.

Are you certain that column B is blank(rows 1 and 2)? or post a sample of your workshett

 

[Evgeny]

Sorry for misunderstanding. I'd like to run this macros for cells with text, not blank.

 

[Momentman]

Meaning it should run while there are no texts maybe numbers and it should cease when it encounters texts,is that it?

 

[Evgeny]

Macros should merge the cells 1 and 2 with any inputs (text, digits). Stop macros if cells are empty.

 

[Momentman]

When you merge rows in the same column, you get a warning that only the upperleftmost value would be displayed

Is this what you want? (Just one value displayed))

what i have done is to suppress that warning in this macro and still merge the cells all the same

Sub mergecells()
    Application.DisplayAlerts = False
    c = 1
        Do
            Range(Cells(1, c), Cells(2, c)).Select
            Selection.mergecells = True
            c = c + 1
        Loop Until Cells(1, c) = "" And Cells(2, c) = ""
    Application.DisplayAlerts = True
End Sub