Autofit Merged Cell Row Height for Some Specific Rows Only

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