Macro for tidying up text

[trevolly]

Hi all,

I have a handover sheet that staff type issues on the airfield and then as the issues get sorted the text is deleted. This means that there are blank lines and when I copy and paste the entries to tidy up the sheet it splits the merges cells in to their original cells. Ive made a vba macro to re-merge all of the cells and re-align the text but as I'm adding cells I've come across the issue of .... "Runtime error 1004, Method range of object global failed" error. The cells from b28:c28 through to b60:c60 need to me re-merged (b into c) and then d28:O28 through to d60:O60 need to be re-merged. This is in single lines not one big cell. Would anyone know a better way of telling excel what rows and columns need to be re-merged?

Ive started a vba code of....

Sub Macro7()
'
' Macro7 Macro
'

'
    Range("B28:C28,D28:O28,B29:C29,D29:O29,B30:C30,D30:O30,B31:C31,D31:O31,B32:C32,D32:O32,B33:c33,d33:o33,b34:c34,d34:o34,b35:c35,d35:o35,b36:c36,d36:o36,b37:c37,d37:o37,b38:c38,d38:o38,b39:c39,d39:o39,b40:c40,d40:o40,b41:c41,d41:o41,b42:c42,d42:o42,b43:c43,d43:o43,b44:c44,d44:o44").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.InsertIndent 1
    Range("D29:O29").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.InsertIndent 1
    Range("B28:C28").Select
    ActiveWindow.SmallScroll Down:=30
    Range("B28:C60").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = -1
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
End Sub

Sub Macro13()
'
' Macro13 Macro
'

'
    Range("D28:O28,D29:O29").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.InsertIndent 1
    Range("B28:C28").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
End Sub

 

[Dan_W]

I think the best solution is to abandon the use of merged cells - they cause nothing but headaches just like this!
Looking at your screen capture, I don't see that you need merged cells at all - there are only two columns. Why not just use columns B and C? Like this:

 

[footoo]

Sub v()
Dim r%
Cells.UnMerge
For r = 28 To 60
    Cells(r, "B").Resize(, 2).MergeCells = True
    Cells(r, "D").Resize(, 12).MergeCells = True
Next
End Sub

 

[footoo]

Or if you want to avoid merged cells :

Sub v()
Dim r%
Cells.UnMerge
For r = 28 To 60
    If Cells(r, "B") <> "" Then _
        Cells(r, "B").Resize(, 2).HorizontalAlignment = xlCenterAcrossSelection
    If Cells(r, "D") <> "" Then _
        Cells(r, "D").Resize(, 12).HorizontalAlignment = xlCenterAcrossSelection
Next
End Sub

 

[trevolly]

I think the best solution is to abandon the use of merged cells - they cause nothing but headaches just like this!
Looking at your screen capture, I don't see that you need merged cells at all - there are only two columns. Why not just use columns B and C? Like this:

View attachment 50903

Thanks @Dan_W - I understand that merged cells are a no no but further up on this sheet are tables that do have merged cells so I cant just stretch out the "C" cell.

 

[Dan_W]

A "no no"? I wouldn't say it's a "no no" (as you say), but it's more that they invariably lead to situations precisely like the one you find yourself in now. But you have your solution.