Macro for tidying up text

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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....
VBA Code:
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
 

Attachments

  • Screenshot 2021-11-05 132127.jpg
    Screenshot 2021-11-05 132127.jpg
    185.7 KB · Views: 16

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
792
Office Version
  1. 365
Platform
  1. Windows
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:

1636570410621.png
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,343
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,343
Office Version
  1. 2016
Platform
  1. Windows
Or if you want to avoid merged cells :
VBA Code:
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

Board Regular
Joined
Aug 22, 2021
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 11, 2018
Messages
792
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,837
Messages
5,766,721
Members
425,373
Latest member
ndiejennrrd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top