Autofit Merged Cell Row Height for Some Specific Rows Only

**Anshu** · 09-22-2020, 08:49 PM

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/thread...-cells.556709/)

Code:

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)

Code:

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

and it works.

But, as soon as I added the third range,

Code:

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/thread...-only.1145918/)

**DocAElstein** · 09-23-2020, 12:10 PM

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\^Col	D	E	F
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

Code:

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\^Col	A	B	C
1	1	1	1
2	1	1	1
3	1	1	1
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\^Col	A	B	C
1	1	1	1
2	1	1	1
3	1	1	1
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\^Col	A	B	C
1	1	1
2			1
3			1
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\^Col	A	B	C	E	F
1	1	1
2			1	1	1
3			1	1	1
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** · 09-23-2020, 06:04 PM

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!!