Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Macro for row height/last row

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

    Default

    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!

  2. #2
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is the data contiguous?

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

    Default

    Yes!!

  4. #4
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub row()
    xrow = 5
    Do While Len(Cells(xrow, 18).Value) > 0
    xrow = xrow + 1
    Loop
    Rows("5:" & xrow).RowHeight = 80
    End Sub


    This should work if the data is contiguous.

  5. #5
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops, make that 90 instead of 80

  6. #6
    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. I guess it will be easy to use, useing Worksheet_Change event.

    Right click on the sheet tab, select View Code and Please copy this into there.


    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Range([A5], [A65536].End(xlUp)).EntireRow.RowHeight = 90
    End Sub

    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

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

    Default

    Zasemmel - tried your code, it works BUT it only makes R5 RowHeight=90, not all the rows with entries(they definitely are contiguous)-any tweaking that may help??

    Thank you!

    Will try the other suggestion and see what happens

  8. #8
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It should work for that whole range assuming there is data in R6 etc. I tested the code myself and it worked fine assuming the cells are contiguous.

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

    Default

    Ok Colo did as instructed - pasted the code but how do I use it now? Do I have to name it and assign it to a macro? Haven't used private sub before, am a novice for sure!

    Thanks!

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

    Default

    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

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
  •