Autofit Merged Cell Row Height for Some Specific Rows Only

[Anshu]

Hello!
I've find the following code which can autofit all protected/unprotected row with merged cells and text wrap on.
(Macro is taken from https://www.mrexcel.com/board/threads/auto-adjust-row-height-merged-cells.556709/)


Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim ProtectStatus As Boolean

With Target
If .MergeCells And .WrapText Then
ProtectStatus = Me.ProtectContents
If ProtectStatus Then Me.Unprotect "password"
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
On Error Resume Next
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
On Error GoTo 0
Application.ScreenUpdating = True
If ProtectStatus Then Me.Protect "password"
End If
End With
End Sub


Requirement:
The code should be applied to some specific range only, not in the whole sheet.

Alternatively,
I want to escape some merged cells from autofit even if the wrap text is on for these cells.


I tried to solve this problem by myself and added a simple line in the code (just before the line With Target)

If Not Intersect(Target, Range("B35:Z35", "D44:R44")) Is Nothing Then
and it works.

But, as soon as I added the third range,

If Not Intersect(Target, Range("B35:Z35", "D44:R44", "G55:Z55")) Is Nothing Then
it stopped working by highlighting the word Range and with the pop up-
"Compile Error: Wrong number of argument or invalid property assignment"

I know, I'm wrong, but where, I don't know.

Thanks!

(This thread is also cross posted at https://www.mrexcel.com/board/threads/autofit-merged-cell-row-height-for-some-specific-rows-only.1145918/)

[DocAElstein]

Hi,
I can answer some of your question, possibly not all. ( I have no experience with protection. Also, like most people using VBA coding, I avoid anything to do with Merged Cells, since they cause a lot of very awkward problems in VBA coding )

I can Explain your error
The short answer is that this
Range("B35:Z35", "D44:R44", "G55:Z55")
should probably be this:
Range("B35:Z35,D44:R44,G55:Z55")

To explain further:in more detail
The mistake you made is a typical mistake often made due to VBA having 2 similar but different syntaxes in the code line part like
__Range(___ )

I will call them Syntax 1 and Syntax 2 for the sake of explanation here. There is little clear documentation on the two different syntaxes. So like you, most people find out about it the hard way when something goes wrong!!

Syntax 1
This takes two arguments. Either can be a string or a range object , usually** these are the Top_Left and Bottom_Right of a single rectangular area.
Pseudo like this
__Range(Top_Left, Bottom_Right)
Syntax 1 returns you a single rectangular area
(**In fact it is not actually necessarily top left and bottom right . In fact, those two arguments set the extremes, and Excel VBA chooses a single rectangular range that encompasses those two ranges.
For Example, VBA interprets Range("F7", "D9:E10") as the entire single area including all encompassing cells here of D7 to F10. ( In VBA jargon – It is one single rectangular area of contiguous cells )
Row\ColCDEFG
6

7
1
1
1

8
1
1
1

9
1
1
1

10
1
1
1

11
So, for example, in order to get those 1s to appear in that range above, we can use this single code line
Range("F7", "D9:E10").Value = 1
More typically, you would likely do it like pseudo __Range(Top_Left, Bottom_Right) , so like
Range("D7", "F10").Value = 1
or various variations on that basic __Range(Top_Left, Bottom_Right) such as
Range(Range("D7"), "F10").Value = 1
or
Range(Cells(7, 4), "F10").Value = 1
Etc… etc…

Your error occurred because you were using this first syntax in your Range(___ ) , but you tried to give it three arguments , not the two that it was expecting.
I don’t personally like Syntax 1. It is a awkward syntax and due to its various forms of taking either a string or a range. It often causes problems/ misunderstandings, unexpected errors due to people not fully understanding the syntax , etc.


I think you probably wanted Syntax 2

Syntax 2
This takes one string argument. But, this string has a syntax that can include as many different Areas as you like



Run this short test macro , in step mode ( using F8 when in the coding in the VB Editor ) , on a spare worksheet, and I think you will see what I mean about the two different syntaxes

Sub Testie() ' https://excelfox.com/forum/showthread.php/2639-Autofit-Merged-Cell-Row-Height-for-Some-Specific-Rows-Only
1 Cells.Clear
2 range("A1:B1", "C2:C3").Value = 1 'Syntax 1 ' This fills range A1:C3
Cells.Clear
range("A1:B1", range("C2:C3")).Value = 1 'Syntax 1 ' This fills range A1:C3
3 Cells.Clear
4 range("A1:C3").Value = 1 'Syntax 2 ' This fills range A1:C3
5 Cells.Clear
6 range("A1:B1,C2:C3").Value = 1 'Snytax 2 ' This fills ranges A1:B1 and C2:C3
7 Cells.Clear
8 'Range("A1:B1", "C2:C3", "E2:F3").Value = 1 ' This errors as the syntax should be pseudo Range(Top_Left, Bottom_Right)
9 Cells.Clear
10 range("A1:B1,C2:C3,E2:F3").Value = 1 'Snytax 2 ' This fills ranges A1:B1 and C2:C3 and E2:F3
End Sub


Code line 2
Syntax 1 ____Range("A1:B1", "C2:C3").Value = 1 ‘ 2 string arguments
_________or
____ ______ Range("A1:B1", Range("C2:C3")).Value = 1 ‘ a string argument and a range object argument
_____ Workbook: Simplified.xlsm ( Using Excel 2007 32 bit )
Row\ColABCD
1111

2111

3111

4
Worksheet: Sheet2


Code line 4
Syntax 2 ___Range("A1:C3").Value = 1 ‘ A single string argjumant
_____ Workbook: Simplified.xlsm ( Using Excel 2007 32 bit )
Row\ColABCD
1111

2111

3111

4
Worksheet: Sheet2


Code line 6
Syntax 2 ___Range("A1:B1,C2:C3").Value = 1 ‘ A single string argjumant
_____ Workbook: Simplified.xlsm ( Using Excel 2007 32 bit )
Row\ColABCD
111

21

31

4
Worksheet: Sheet2


Code line 8
False use of Syntax 1 _ Range("A1:B1", "C2:C3", "E2:F3").Value = 1
This last code line will error because you tried to use 3 string arguments : You need to use a single string argument, Syntax 2, as in the next example



Code line 10
Syntax 2 ___Range("A1:B1,C2:C3,E2:F3").Value = 1 ‘ A single string argjumant
_____ Workbook: Simplified.xlsm ( Using Excel 2007 32 bit )
Row\ColABCDEFG
111

2111

3111

4
Worksheet: Sheet2



In your example you used this false use of syntax 1
Range("B35:Z35", "D44:R44", "G55:Z55")
You probably wanted Syntax 2
Range("B35:Z35,D44:R44,G55:Z55")




Note also: When you used
Range("B35:Z35", "D44:R44")
You were possibly not doing exactly what you wanted: That code part returned you a single rectangular range with top left of B35 , and bottom right of Z44
You probably wanted a range object of two rectangular Areas , B35:Z35 and D44:R44
For that range object of two areas you needed syntax 2 once again. Like
Range("B35:Z35,D44:R44")


Alan

[Anshu]

Really Nice Explanation with every steps, points and highlights! Now I understand where was the fault. Apart from the knowledge, this thread also enriched my experience about seeing the VBA problem with a different view to solve it effectively.

The solution is working well, without any issue.

Thank you for this solution and explanation!!