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

[tlc53]

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!

[TABLE="width: 500"]
<tbody>[TR]
[TD]No. of Accounts[/TD]
[TD]Row 46 Height[/TD]
[TD]Hide Rows[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]140[/TD]
[TD]22-35[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]120[/TD]
[TD]23-35[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]100[/TD]
[TD]24-35[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]80[/TD]
[TD]25-36[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]60[/TD]
[TD]26-35[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]40[/TD]
[TD]27-35[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]28-35[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]29-35, 39[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]30-35, 39-40[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]31-35, 39-41[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]32-35, 39-42[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]33-35, 39-43[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]34-35, 39-44[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]35, 39-45[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]39-46[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

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?

 

[tlc53]

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.

 

[JoeMo]

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.

 

[tlc53]

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!

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

 

[JoeMo]

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:

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.

 

[tlc53]

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.

 

[JoeMo]

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:

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

 

[tlc53]

Sure, just forget about the Target lines. Something like this:

I love it when a plan comes together. Thank you! Works great

 

[JoeMo]

I love it when a plan comes together. Thank you! Works great

You are welcome - thanks for the reply.