Use of Range function

[dhcollegedude]

Hello Everyone,

I am using VBA for macros for use with an Excel spreadsheet I have been working on. The goal of the macro is to change 7th row's height to match that of a template.

In my first attempt I tried simply looping through the rows changing every seventh row's height and then moving on to the next one. Unfortunately, due to the size of the spreadsheet and other activities occurring at the same time, this became very inefficient. As an alternative I have already gathered a list of the rows to be changed in the following format into a string called Lst:

"1:1,8:8,15:15,...,n:n" where n is the last row with data in it

Then I use the string in the following line of code:

Range(Lst).RowHeight = Worksheets("Template").Range("A" & 1).RowHeight

Now this works until there is data in or past row 252. The sample I am using as a test has data until row 344. For this sample Lst should have the following value:

"1:1,8:8,15:15,22:22,29:29,36:36,43:43,50:50,57:57 ,64:64,71:71,78:78,85:85,92:92,99:99,106:106,113:1 13,120:120,127:127,134:134,141:141,148:148,155:155 ,162:162,169:169,176:176,183:183,190:190,197:197,2 04:204,211:211,218:218,225:225,232:232,239:239,246 :246,253:253,260:260,267:267,274:274,281:281,288:2 88,295:295,302:302,309:309,316:316,323:323,330:330 ,337:337,344:344"

To test the data I used MsgBox Lst, which does agree. To the contrary, the debugger which comes with Excel shows Lst to have the following value:

"1:1,8:8,15:15,22:22,29:29,36:36,43:43,50:50,57:57 ,64:64,71:71,78:78,85:85,92:92,99:99,106:106,113:1 13,120:120,127:127,134:134,141:141,148:148,155:155 ,162:162,169:169,176:176,183:183,190:190,197:197,2 04:204,211:211,218:218,225:225,232:232,239:239,246 :246,

Could someone help me resolve this issue? Thank you in advance.

SOLUTION - ** SNIP **

[NeoPa]

Although the string length can go into the millions, the string value passed as the Cell1 parameter to Range cannot exceed 256 without this failure occurring.

The following code will handle this if required, by breaking up the list of rows into strings which are each less than 256 long and setting the .RowHeight property for each one :

Option Explicit
 
Public Sub SetRowHeight(lngMaxRow As Long)
    Dim strRows As String, strWork As String
    Dim lngX As Long
    Dim dblRowHeight As Double
 
    dblRowHeight = Worksheets("Template").Range("A1").RowHeight
    For lngX = 1 To lngMaxRow Step 7
        strRows = strRows & Replace(",R:R", "R", lngX)
    Next lngX
    strRows = Mid(strRows, 2)
    Do While strRows > ""
        If Len(strRows) > 256 Then
            lngX = InStr(240, strRows, ",") - 1
        Else
            lngX = Len(strRows)
        End If
        strWork = Left(strRows, lngX)
        Range(strWork).RowHeight = dblRowHeight
        strRows = Mid(strRows, lngX + 2)
    Loop
End Sub

[NeoPa]

I did try similar code but using the Union() method instead (See below), but it ran a great deal more slowly.

Option Explicit
 
Public Sub SetRowHeight(lngMaxRow As Long)
    Dim strRows As String, strWork As String
    Dim lngX As Long
    Dim ranList As Range
 
    Set ranList = Range("1:1")
    For lngX = 8 To lngMaxRow Step 7
        strRows = strRows & Replace(",R:R", "R", lngX)
    Next lngX
    strRows = Mid(strRows, 2)
    Do While strRows > ""
        If Len(strRows) > 256 Then
            lngX = InStr(240, strRows, ",") - 1
        Else
            lngX = Len(strRows)
        End If
        strWork = Left(strRows, lngX)
        Set ranList = Union(ranList, Range(strWork))
        strRows = Mid(strRows, lngX + 2)
    Loop
    ranList.RowHeight = Worksheets("Template").Range("A1").RowHeight
End Sub