Using Case Statement to adjust row height

[ABabeNChrist]

I’m trying to create a command button that will expand row height on activecell to a couple preset heights then when maximum height is reached it would then return to lowest setting. I first tried If then statements with no success, now I’m trying to use Case statements. Here’s what I have so far.

Code: Select all

    Select Case ActiveCell.RowHeight

    Case ActiveCell.RowHeight = 110

        ActiveCell.RowHeight = 220
        MsgBox ("Row adjustment enlarged")

    Case ActiveCell.RowHeight = 220

        ActiveCell.RowHeight = 330
        MsgBox ("Row adjustment enlarged")

    Case ActiveCell.RowHeight = 330

        ActiveCell.RowHeight = 440
        MsgBox ("Row adjustment enlarged")

    Case ActiveCell.RowHeight = 440

        ActiveCell.RowHeight = 110
        MsgBox ("Row adjustment reduced")

    End Select

[Rick Rothstein]

You do not need a Select Case block at all; you can do the increments (including the wrap-around) with one line of code...

Code: Select all

ActiveCell.RowHeight = 110 + (ActiveCell.RowHeight Mod 440)
MsgBox ("Row adjustment enlarged")

EDIT: Taking a cue from Hans' message, here is my code using 100 as the increment and 400 as the wrap-around point (and I agree with him about the MessageBox)...

Code: Select all

ActiveCell.RowHeight = 100 + (ActiveCell.RowHeight Mod 400)

[HansV]

Maximum row height is 409 points (see Excel specifications and limits) so you won't be able to set the row height to 440 points. You could use this:

Code: Select all

Sub ChangeRowHeight()
  If ActiveCell.RowHeight > 300 Then
    ActiveCell.RowHeight = 100
  Else
    ActiveCell.RowHeight = ActiveCell.RowHeight + 100
  End If
End Sub

I omitted the message boxes - they become irritating quickly.

[ABabeNChrist]

Thank you Rick and Hans for these great options
Hans when I first tried using the If then statement I didn’t have much success but I forgot to include “>” as in your suggestion. Very cool thanks

[Rick Rothstein]

Actually, mine does not work because the RowHeight does not start at 0 (like the variable I used in my test did). Come back to the thread a little later, though, as I am sure there is a simple one-liner available.

[Rick Rothstein]

Okay, here is the one-liner solution I mention earlier...

Code: Select all

ActiveCell.RowHeight = 100 + ((100 * Int(ActiveCell.RowHeight / 100)) Mod 400)

[ABabeNChrist]

Now that’s a good 1 liner
Thank Rick

[ABabeNChrist]

I was trying to add another element to this code that would first unhide a determined row then hide row when row reaches a certain size. The reason for this type of code is I’m planning on using this row to insert photos if so needed along with the use of another piece of code that will place photo in selected cell in this row at a predetermined height size of 100. If this present row height is full of photos and more room is needed then by selecting button once more the row would be adjusted + 100 to accommodate more photos, and of course even once more if so needed. If row is then not needed by selecting button one last time the row would then be hidden. I hope I explained this OK. Does this code look correct?

Code: Select all

Private Sub CommandButton1_Click()
    With ActiveSheet.Rows("7:7")
        .Hidden = False
        .Rows.Select
    End With

    If ActiveSheet.Rows("7:7").RowHeight > 300 Then
        ActiveSheet.Rows("7:7").RowHeight = 100
    Else
        ActiveSheet.Rows("7:7").RowHeight = ActiveCell.RowHeight + 100
    End If

    If ActiveSheet.Rows("7:7").RowHeight = 100 Then
        With ActiveSheet.Rows("7:7")
            .RowHeight = 1
            .Hidden = True
        End With
    End If
End Sub

[HansV]

You make it more complicated than necessary. Try

Code: Select all

Private Sub CommandButton1_Click()
    With ActiveSheet.Rows("7:7")
        If .RowHeight > 300 Then
            .RowHeight = 0
        Else
            .RowHeight = .RowHeight + 100
        End If
    End With
End Sub

or using Rick's on-liner:

Code: Select all

Private Sub CommandButton1_Click()
    ActiveSheet.Rows("7:7").RowHeight = (100 + (100 * Int(ActiveSheet.Rows("7:7").RowHeight / 100))) Mod 500
End Sub

[ABabeNChrist]

That works perfect. I noticed by setting to 0 it served the same purpose as hiding the row, thank you