PartyPanda
06-09-2016, 03:54 PM
Hi There,
I'm quite new to VBA and have subs that automatically adjusts row heights for me in 14 separate sheets. The sub is almost identical in each sheet, just the ar Array changes as the rows I want to adjust are not all the same in each sheet. When I am in the active sheet and run the macro 'FixMerged' (it is assigned to a button), it works perfectly, and does exactly what it should.
Since I have 14 sheets and I need to run 14 macros, I wanted to create one short macro to run them all at once using the Call() rather than having to go into each sheet and clicking the buttons to run the macro.
For some reason though, when I run the macro by calling it, it doesn't work properly. What happens is all the rows just adjust to the height of 40, which it's grabbing from this chunk of code here:
rng.RowHeight = rwht
If rng.RowHeight < 40 Then
rng.RowHeight = 40
Else
rng.RowHeight = rwht
End If
Here is the row height adjust macro:
Option ExplicitSub FixMerged() 'Excel VBA to autofit merged cells
ActiveSheet.Unprotect Password:="PASSWORD"
Dim mw As Single
Dim cM As Range
Dim rng As Range
Dim cw As Double
Dim rwht As Double
Dim ar As Variant
Dim i As Integer
Application.ScreenUpdating = False
'Cell Ranges below, change to suit.
ar = Array("B8", "B13", "B16", "B20", "B24", "B28", "B30", "B35", "B36", "B41", "B45")
For i = 0 To UBound(ar)
On Error Resume Next
Set rng = Range(Range(ar(i)).MergeArea.Address)
rng.MergeCells = False
cw = rng.Cells(1).ColumnWidth
mw = 0
For Each cM In rng
cM.WrapText = True
mw = cM.ColumnWidth + mw
Next
mw = mw + rng.Cells.Count * 0.66
rng.Cells(1).ColumnWidth = mw
rng.EntireRow.AutoFit
rwht = rng.RowHeight
rng.Cells(1).ColumnWidth = cw
rng.MergeCells = True
rng.RowHeight = rwht
If rng.RowHeight < 40 Then
rng.RowHeight = 40
Else
rng.RowHeight = rwht
End If
rng.Cells.Locked = False
Next i
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="PASSWORD", AllowFormattingRows:=True
End Sub
^^ This same macro lives in 14 sheets, and again, only the ar Array is different
And then in the 15th sheet I have the following macro assigned to a button:
Option Explicit
Sub RunAllFixMerged()
Call Sheet13.FixMerged
Call Sheet14.FixMerged
...
... (and so on 12 more times)
End Sub
So for example, if I am in Sheet13 and run macro FixMerged() it works properly, or if I run RunAllFixMerged, it works properly. But If I run the macro RunAllFixMerged from any sheet other than the one I'm in, it'll adjust all the row heights to 40.
Any input would be appreciated!! I'm really stuck here!
I'm quite new to VBA and have subs that automatically adjusts row heights for me in 14 separate sheets. The sub is almost identical in each sheet, just the ar Array changes as the rows I want to adjust are not all the same in each sheet. When I am in the active sheet and run the macro 'FixMerged' (it is assigned to a button), it works perfectly, and does exactly what it should.
Since I have 14 sheets and I need to run 14 macros, I wanted to create one short macro to run them all at once using the Call() rather than having to go into each sheet and clicking the buttons to run the macro.
For some reason though, when I run the macro by calling it, it doesn't work properly. What happens is all the rows just adjust to the height of 40, which it's grabbing from this chunk of code here:
rng.RowHeight = rwht
If rng.RowHeight < 40 Then
rng.RowHeight = 40
Else
rng.RowHeight = rwht
End If
Here is the row height adjust macro:
Option ExplicitSub FixMerged() 'Excel VBA to autofit merged cells
ActiveSheet.Unprotect Password:="PASSWORD"
Dim mw As Single
Dim cM As Range
Dim rng As Range
Dim cw As Double
Dim rwht As Double
Dim ar As Variant
Dim i As Integer
Application.ScreenUpdating = False
'Cell Ranges below, change to suit.
ar = Array("B8", "B13", "B16", "B20", "B24", "B28", "B30", "B35", "B36", "B41", "B45")
For i = 0 To UBound(ar)
On Error Resume Next
Set rng = Range(Range(ar(i)).MergeArea.Address)
rng.MergeCells = False
cw = rng.Cells(1).ColumnWidth
mw = 0
For Each cM In rng
cM.WrapText = True
mw = cM.ColumnWidth + mw
Next
mw = mw + rng.Cells.Count * 0.66
rng.Cells(1).ColumnWidth = mw
rng.EntireRow.AutoFit
rwht = rng.RowHeight
rng.Cells(1).ColumnWidth = cw
rng.MergeCells = True
rng.RowHeight = rwht
If rng.RowHeight < 40 Then
rng.RowHeight = 40
Else
rng.RowHeight = rwht
End If
rng.Cells.Locked = False
Next i
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="PASSWORD", AllowFormattingRows:=True
End Sub
^^ This same macro lives in 14 sheets, and again, only the ar Array is different
And then in the 15th sheet I have the following macro assigned to a button:
Option Explicit
Sub RunAllFixMerged()
Call Sheet13.FixMerged
Call Sheet14.FixMerged
...
... (and so on 12 more times)
End Sub
So for example, if I am in Sheet13 and run macro FixMerged() it works properly, or if I run RunAllFixMerged, it works properly. But If I run the macro RunAllFixMerged from any sheet other than the one I'm in, it'll adjust all the row heights to 40.
Any input would be appreciated!! I'm really stuck here!