Macro Question for Dave Peterson

  • Thread starter Thread starter JoeSpareBedroom
  • Start date Start date
J

JoeSpareBedroom

Dave:
Last week, you provided the macro below. If you recall, the goal was to look
at the row heights of the first 5 rows, skip row 6, and "copy" the row
heights to rows 7-11. After running this, nothing appears to happen, but
there are no error messages. Granted, I've only briefly studied this, but is
this chunk of code supposed to just look at the first 5 rows and assign
variables, or is it supposed to continue and duplicate what it finds?
Incidentally, I did not assign this to a button. I ran it from the Tools,
Macros menu.


Option Explicit
Sub testme()
Dim iRow As Long
With Worksheets("sheet1")
For iRow = 7 To .Cells.SpecialCells(xlCellTypeLastCell).Row
.Rows(iRow).RowHeight = .Rows(((iRow - 2) Mod 5) + 1).RowHeight
Next iRow
End With
End Sub
 
I changed the ("sheet1") in your code to match the actual name of my sheet,
"Feb 06 2007". Other specifics: I placed the code in the workbook itself,
not in personal.xls. To be sure I was placing it correctly, I began by
recording a short macro (just some typing), so when I opened the editor, it
would be easy to spot where to paste your code. Saved the workbook, and ran
the code.
 
IF I understand Dave's code correctly, it will not work if ONLY rows 1 to 5
have their row heights changed because
".Cells.SpecialCells(xlCellTypeLastCell).Row" will evaluate to 5 and the
"irow loop" will not execute.

Assuming that my supposition is true:

The modified code below will repeat the height changes 10 times (7-11),
(13-17) etc so modify the line "For n = 1 To 10" to suit i.e. change 10 to
what is required.


Hope you can use this.

Sub testme()
Dim iRow As Long, n As Long
With Worksheets("sheet1")
For n = 1 To 10
For iRow = (n - 1) * 6 + 7 To (n - 1) * 6 + 11
.Rows(iRow).RowHeight = .Rows(iRow Mod 6).RowHeight
Next iRow
Next n
End With
End Sub


JoeSpareBedroom said:
I changed the ("sheet1") in your code to match the actual name of my sheet,
"Feb 06 2007". Other specifics: I placed the code in the workbook itself,
not in personal.xls. To be sure I was placing it correctly, I began by
recording a short macro (just some typing), so when I opened the editor, it
would be easy to spot where to paste your code. Saved the workbook, and ran
the code.
Click to expand...
 
You're right. I assumed that there was actual data in the rows 7:whatever.

If the OP is setting this up in an (almost) empty worksheet, then I would have
changed:

For iRow = 7 To .Cells.SpecialCells(xlCellTypeLastCell).Row
To:
For iRow = 7 To 999 '999 as big as required.
IF I understand Dave's code correctly, it will not work if ONLY rows 1 to 5
have their row heights changed because
".Cells.SpecialCells(xlCellTypeLastCell).Row" will evaluate to 5 and the
"irow loop" will not execute.

Assuming that my supposition is true:

The modified code below will repeat the height changes 10 times (7-11),
(13-17) etc so modify the line "For n = 1 To 10" to suit i.e. change 10 to
what is required.

Hope you can use this.

Sub testme()
Dim iRow As Long, n As Long
With Worksheets("sheet1")
For n = 1 To 10
For iRow = (n - 1) * 6 + 7 To (n - 1) * 6 + 11
.Rows(iRow).RowHeight = .Rows(iRow Mod 6).RowHeight
Next iRow
Next n
End With
End Sub
Click to expand...
 
Thanks very much! That worked like a charm. Now, I can dissect that FOR loop
and figure out just how you did it. I find it easier to learn this stuff
from working examples. No hints, please!


Toppers said:
IF I understand Dave's code correctly, it will not work if ONLY rows 1 to
5
have their row heights changed because
".Cells.SpecialCells(xlCellTypeLastCell).Row" will evaluate to 5 and the
"irow loop" will not execute.

Assuming that my supposition is true:

The modified code below will repeat the height changes 10 times (7-11),
(13-17) etc so modify the line "For n = 1 To 10" to suit i.e. change 10 to
what is required.


Hope you can use this.

Sub testme()
Dim iRow As Long, n As Long
With Worksheets("sheet1")
For n = 1 To 10
For iRow = (n - 1) * 6 + 7 To (n - 1) * 6 + 11
.Rows(iRow).RowHeight = .Rows(iRow Mod 6).RowHeight
Next iRow
Next n
End With
End Sub
Click to expand...
 
Back
Top