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:113,120:120,127:127,134:134,141:141,148:148,155:155,162:162,169:169,176:176,183:183,190:190,197:197,204:204,211:211,218:218,225:225,232:232,239:239,246:246,253:253,260:260,267:267,274:274,281:281,288:288,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:113,120:120,127:127,134:134,141:141,148:148,155:155,162:162,169:169,176:176,183:183,190:190,197:197,204:204,211:211,218:218,225:225,232:232,239:239,246:246,

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

[rylo]

Hi

How about

Dim myrng As Range
  Set myrng = Range("A1")
  For i = 8 To 500 Step 7
    Set myrng = Union(myrng, Cells(i, 1))
  Next i
  
  myrng.EntireRow.RowHeight = worksheets("Template").range("A1").rowheight

rylo

[dhcollegedude]

Thank you. That helped a lot. Had to change one part:

Set myrng = Union(myrng, Range(i & ":" & i))

It wouldn't work for some reason with Cells(i,1)


Thank you again.

[smuzoen]

Just for my own interest does Range have a finite number of parameters because the following works

Sub test()
Lst = "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:113,120:120,127:127,134:134,141:141,148:148,155:155,162:162,169:169,176:176,183:183,190:190,197:197" & _
",204:204,211:211,218:218,225:225,232:232"
Range(Lst).RowHeight = Worksheets("Template").Range("A" & 1).RowHeight
Lst = "239:239,246:246,253:253,260:260,267:267,274:274,281:281,288:288,295:295,302:302,309:309,316:316,323:323,330:330,337:337,344:344"
Range(Lst).RowHeight = Worksheets("Template").Range("A" & 1).RowHeight
End Sub

[dhcollegedude]

That was actually one thing I had tried before rylo supplied a better solution. The problem with breaking it up into separate strings would make it not as easily adaptive to new data, which I need mine to be. However, from my understanding so far, and someone please correct me if mistaken, but it looks like Range can only take in a string of limited length.