Macro for row height/last row

[chookers]

Hi, I'm trying to get a macro that will select my rows, beginning at R5, and set the RowHeight at 90 for R5 through whatever the last row with entries is (this will change fairly regularly - one day it'll be 8 rows, the next day maybe 17 rows)

I know how to select the row height, but don't know how it should be combined with more code. This is just going to be used for when the info is printed out to make it more reader friendly.

Any help much appreciated! Thank you!

 

[Colo]

Hello again. When change value of a cell, it will be started automatically.

Please enter something.

 

[chookers]

Oh Colo that is really tricky! That's a good one. I may be able to use it but what I was actually going to do is 2 macros buttons, one that would format the rowheight to 90 and one that would format it to 120, so in other words when someone wants to print the worksheet they can, depending on the instance, print it so it's with either 3 rows OR 4 rows per page, depending what works best for their particular project.

I know it sounds a bit weird, it's just to make the thing more user/reader friendly. The only way I can do this is to give the user a choice of rowheight 90 or rowheight 120 buttons.

Your idea is so cool though....

Thank you very much!

 

[Ivan F Moala]

On 2002-04-30 21:24, chookers wrote:
Zacemmel (Jeez did I spell that right?) Yes Just my luck it would work for you and not me! Rats! Am pasting it below, I've got info in R6 - R11 on this particular sheet and it's still only doing it for R6 (I meant to say R6 earlier, not R5 - sorry1)

Well if this is what's working for you it must be my worksheet? Thanks for your help anyway! If anyone else sees a solution...help!

Cheers!
Dim xrow As String
xrow = 6
Do While Len(Cells(xrow, 18).Value) > 0
xrow = xrow + 1
Loop
Rows("6:" & xrow).RowHeight = 90

Colo's code adjusted worked for me.

<pre/>
Sub TTest()
Range([R6], [R65536].End(xlUp)).EntireRow.RowHeight = 90
End Sub
</pre>

 

[chookers]

yes yes, thank you Ivan - Colo's code worked for me as written, I was just looking for more of a macro thing so I could give the user the choice (via 2 different toolbar buttons) of displaying the doc with EITHER row height 90 OR 120 for printing purposes.

Thank you!

 

[chookers]

Oh there must be more help out there! Z's code nearly worked, but the code,at least in my workbook, only changed the rowheight of the one row, not of rows from 5 (well it should have been 6)through LastRow.

How on earth can we select R6 through the last row with entries, and change row height to 90 (colo's works great but I need to assign the code to a button, so I need to keep looking)

Heeeeeeeeeeellppppppp!!

 

[Colo]

Hi chookers, I made a smple for you.
Please copy this into a standard module.
And run MakeButton. Hope this help.

<pre>

Option Base 1
Sub MakeButton()
Dim rngBtn As Range, objBtn As Button, arr, intCnt As Integer
arr = Array(90, 120)
For intCnt = 1 To 2
Set rngBtn = Application.InputBox("Pls select where you wanna make a button", Type:=8)
With rngBtn
Set objBtn = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
End With
With objBtn
.OnAction = "RH" & arr(intCnt)
.Characters.Text = "RowH" & arr(intCnt)
End With
Next
End Sub

Sub RH90()
Call ChangeRowHeight(90, Range([R5], [R65536].End(xlUp)).EntireRow)
End Sub

Sub RH120()
Call ChangeRowHeight(120, Range([R5], [R65536].End(xlUp)).EntireRow)
End Sub

Sub ChangeRowHeight(H, rngRow As Range)
rngRow.RowHeight = H
End Sub

</pre>

 

[chookers]

Dear Colo, are you trying to give me a heartattack? Ok, I've put the code, which is very impressive and totally incomprehensible to me, in a module and named it MakeButton.

When I run MakeButton and the input box comes up, what is my answer supposed to be to 'Pls select where you wanna make a button?' I have looked for obvious clues in the code and have tried different things, only getting reference errors.

Well I will be so impressed if this winds up working, it looks insane.

I am in Australia and am leaving work in about 5 minutes so I will check this again tomorrow morning.

Thank you so very much (I think!).

Cheers, Lauren oops Chookers

 

[chookers]

Colo -- Ok I got it! Thank you so much, it works great! YOu're a legend, mate!

- only wish I had enough experience to understand it all --- I'll get there someday.

Cheers! Thanks again!