Results 1 to 10 of 10

Thread: VBA Code - Macro to Auto Format by hiding and/or resizing rows

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code - Macro to Auto Format by hiding and/or resizing rows

    Hi,

    I have certain formatting (hiding of rows or resizing of a row) that needs to occur if new accounts are added/removed.
    I will have a macro button which can be run if they make any changes to the accounts.
    Cell K36 has a count formula and tells you how many accounts there are. Depending on this number, the below action of Row 46 Height and Hide Rows, needs to take place. The default size of Row 46 is 20.
    If someone could just get me started and in the right direction, that would be great. Thank you!

    No. of Accounts Row 46 Height Hide Rows
    1 140 22-35
    2 120 23-35
    3 100 24-35
    4 80 25-36
    5 60 26-35
    6 40 27-35
    7 28-35
    8 29-35, 39
    9 30-35, 39-40
    10 31-35, 39-41
    11 32-35, 39-42
    12 33-35, 39-43
    13 34-35, 39-44
    14 35, 39-45
    15 39-46

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,482
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Code - Macro to Auto Format by hiding and/or resizing rows

    A few questions:
    1. Row 46 and the rows to be hidden: are they on the same sheet as the macro button?
    2. The table you show: is it on the sheet or could it be placed on another sheet? If yes, what range will it occupy?
    3. What's the height of row 46 for 7 or more accounts?
    4. Will there ever be more than 15 accounts?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Macro to Auto Format by hiding and/or resizing rows

    Quote Originally Posted by JoeMo View Post
    A few questions:
    1. Row 46 and the rows to be hidden: are they on the same sheet as the macro button?
    2. The table you show: is it on the sheet or could it be placed on another sheet? If yes, what range will it occupy?
    3. What's the height of row 46 for 7 or more accounts?
    4. Will there ever be more than 15 accounts?
    1. Yes, the macro button will be on the same sheet Row 46 and the other rows that need to be hidden.
    2. No, the table is not currently in the workbook or on a worksheet. I was thinking that the criteria gets hard wired into the VBA coding and then I will add a macro button to each sheet/account. There are quite a few sheets I will add this macro to but the cell references regardless of the sheet, are always the same. I could create a sheet to store the data on if that's easier.
    3. Row 46 default height is 20
    4. No, there will not be more than 15 accounts.

    Thanks.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,482
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Code - Macro to Auto Format by hiding and/or resizing rows

    Quote Originally Posted by tlc53 View Post
    1. Yes, the macro button will be on the same sheet Row 46 and the other rows that need to be hidden.
    2. No, the table is not currently in the workbook or on a worksheet. I was thinking that the criteria gets hard wired into the VBA coding and then I will add a macro button to each sheet/account. There are quite a few sheets I will add this macro to but the cell references regardless of the sheet, are always the same. I could create a sheet to store the data on if that's easier.
    3. Row 46 default height is 20
    4. No, there will not be more than 15 accounts.

    Thanks.
    That's a lot of info to hard wire into a macro. I would be inclined to create a sheet called, say, FormatInfo with the info as you have listed it in cols A:C for reference. I assume that 7 or more accounts gets the default row height for row 46.

    It's bedtime for me. If no one has provided you a solution when I'm back online, I'll have a shot at it.
    Last edited by JoeMo; Sep 22nd, 2019 at 11:02 PM.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Macro to Auto Format by hiding and/or resizing rows

    Quote Originally Posted by JoeMo View Post
    That's a lot of info to hard wire into a macro. I would be inclined to create a sheet called, say, FormatInfo with the info as you have listed it in cols A:C for reference. I assume that 7 or more accounts gets the default row height for row 46.

    It's bedtime for me. If no one has provided you a solution when I'm back online, I'll have a shot at it.
    Thanks Joe. I have an idea of how I was hoping it would work and I have pieced together the below. Unsurprisingly, it doesn't work
    The idea is that it's stored under a module and run by a macro button. The opening code is not right but I'm not sure what I should be putting instead. If you could have a gander, that would be great. Thank you!

    Code:
    Sub Print_Setup()
    
    
    If Not Intersect(Target, Range("K36")) Is Nothing Then
    
    
    If Target.Cells.CountLarge > 1 Then Exit Sub
    Select Case Target.Value
    
    
    
    
        Case 0
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("46:46").EntireRow.RowHeight = 20
            
        Case 1
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("22:35").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 140
            
        Case 2
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("23:35").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 120
            
        Case 3
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("24:35").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 100
                    
        Case 4
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("25:35").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 80
                    
        Case 5
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("26:35").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 60
                    
        Case 6
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("27:35").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 40
                    
        Case 7
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("28:35").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 20
                    
        Case 8
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("29:35,39:39").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 20
                    
        Case 9
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("30:35,39:40").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 20
                    
        Case 10
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("31:35,39:41").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 20
                    
        Case 11
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("32:35,39:42").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 20
                    
        Case 12
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("33:35,39:43").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 20
                    
        Case 13
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("34:35,39:44").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 20
                    
        Case 14
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("35:35,39:45").EntireRow.Hidden = True
            Range("46:46").EntireRow.RowHeight = 20
                    
        Case 15
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("39:46").EntireRow.Hidden = True
    End Select
    End If
    End Sub

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,482
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Code - Macro to Auto Format by hiding and/or resizing rows

    Good start except that this is a standard macro not an event macro so "target" has no meaning. What you want is an event macro that will respond automatically to some event. Assuming a change in the cell holding the countif formula would be a good trigger for adjusting row height and hidden rows, I would suggest using a calculate_event. Something like this:
    Code:
    Private Sub Worksheet_Calculate()
    If Not IsEmpty(Range("K36")) Then
           Select Case Range("K36").Value
    'your code here
    
    
    End Sub
    Alternatively, you could use a worksheet_activate code to trigger row height and hidden rows adjustments whenever a specific worksheet is selected. This might be a better way to go, but hard for me to tell as I have very little understanding of what you want.

    EDIT: Also, don't forget to unhide all rows before you do your select case so that rows hidden in a prior code execution do not remain hidden if the current execution is for a different case.
    Last edited by JoeMo; Sep 23rd, 2019 at 12:04 PM.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  7. #7
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Macro to Auto Format by hiding and/or resizing rows

    This code will potentially be used on 110 sheets. Each sheet represents a client and an invoice is automatically created from it.
    Account codes can be added to sheets however, this puts the print alignment out. That's where this piece of code comes into play.
    One thing I really want to avoid, is having automatic VBA coding that slows the spreadsheet down. It's already doing quite a lot. This was why I was thinking that the user could manually activate the macro, which would check what the number is in K36 and apply the settings accordingly. The macro button will read "Accounts added/removed? Run print setup alignment".
    Is there a way to accomplish this?
    In my code, the first line of code under each case is to unhide all rows, and then apply the hide rows applicable depending on count in cell K36.

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,482
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Code - Macro to Auto Format by hiding and/or resizing rows

    Quote Originally Posted by tlc53 View Post
    This code will potentially be used on 110 sheets. Each sheet represents a client and an invoice is automatically created from it.
    Account codes can be added to sheets however, this puts the print alignment out. That's where this piece of code comes into play.
    One thing I really want to avoid, is having automatic VBA coding that slows the spreadsheet down. It's already doing quite a lot. This was why I was thinking that the user could manually activate the macro, which would check what the number is in K36 and apply the settings accordingly. The macro button will read "Accounts added/removed? Run print setup alignment".
    Is there a way to accomplish this?

    In my code, the first line of code under each case is to unhide all rows, and then apply the hide rows applicable depending on count in cell K36.
    Sure, just forget about the Target lines. Something like this:
    Code:
    Sub Print_Setup()
    If Not IsEmpty(Range("K36")) And Not IsError(Range("K36")) Then
        Select Case Range("K36").Value
        Case 0
            Range("21:35,39:46").EntireRow.Hidden = False
            Range("46:46").EntireRow.RowHeight = 20
            
        'rest of the cases
        End Select
    Else
        MsgBox "Cell K36 must contain a numerical value - check and adjust, then run this macro again"
    End If
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #9
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Macro to Auto Format by hiding and/or resizing rows

    Quote Originally Posted by JoeMo View Post
    Sure, just forget about the Target lines. Something like this:
    I love it when a plan comes together. Thank you! Works great

  10. #10
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,482
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Code - Macro to Auto Format by hiding and/or resizing rows

    Quote Originally Posted by tlc53 View Post
    I love it when a plan comes together. Thank you! Works great
    You are welcome - thanks for the reply.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

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
  •