Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Macro for row height/last row

  1. #11
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

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

    Please enter something.
    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  2. #12
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  3. #13
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


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


    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #14
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #16
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

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



    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


    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  7. #17
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #18
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •