How to change column width and row height

[JenniferMurphy]

I just learned a very helpful way to change a range of columns to a set width using paste special from this page:

Change the column width and row height

But of course, in typical m$ft fashion, they only provide half-baked solutions. Even though the title says "column width and row height", the text only deals with column widths. And the paste special options do not include row height as an option.

Is there any comparable way to change a bunch of rows to the same specific height? I don't mean by dragging the border. I mean by matching some existing cell height like what works for columns.

 

[JenniferMurphy]

Jennifer,

Couldn't you, for each sheet, change the values of the variables? That is my idea...
Maybe i'm not understanding what you're trying to do...

M.

If you mean edit the Sub, sure, I could do that, but it would be a royal pain in the knickers.

With columns, my tables need to grow and shrink to fit the data. But I often have titles above the tables that are wider than the data. The titles are set to expand into adjacent cells, so I can't just select all of the table columns and double-click the rightmost border to get Excel to auto-fit them because it will fit those titles, which are way too wide. This handy trick of selecting one cell allows me to auto-fit one column, one with no titles, and then match all of the other columns to it. Just 3 quick steps and done.

I would like something like that for the rows. If m$ft had any commitment to its users, there would be a similar feature for the rows. I was just asking if there might be something I don't know about. But having to edit a Sub every time isn't going to work. And I would have to copy that Sub to every workbook, because the geniuses at m$ft did not provide a way for a sheet to access a Sub in an add-in module. (sigh) And that would make them all .xlsm workbooks. Just another pof the many joys of working with m$ft products.

 

[GWteB]

If I want to run this from a sheet, how do I get the row numbers that I want passed to either Sub?

In one go that's not possible. After all, you FIRST will have to select a cell whose height you want to pass to the procedure, which procedure in turn wants to be informed of the TARGET rows as well. How do we inform that procedure about the target rows? We can't, at least not in one go. So what do you have in mind? Is a procedure that you have to call twice in succession from Macros Dialog (ALT F8) a suitable way for you?

 

[JenniferMurphy]

In one go that's not possible. After all, you FIRST will have to select a cell whose height you want to pass to the procedure, which procedure in turn wants to be informed of the TARGET rows as well. How do we inform that procedure about the target rows? We can't, at least not in one go. So what do you have in mind? Is a procedure that you have to call twice in succession from Macros Dialog (ALT F8) a suitable way for you?

No, not really. It doesn't come up that often. It mostly has to do with columns. I actually wouldn't use it that much for rows. I was just annoyed at yet another instance of m$ft failing to provide consistency and common sense.

Thanks to you and Marcelo for your efforts. I apprediate it.

 

[GWteB]

You are welcome.

 

[GWteB]

Might have an alternative that suits.
Select a single cell which height will become the reference height. Then select (using CTRL and SHIFT) the cells (or entire rows) of which the row height has to be adjusted. Finaly hold down Left CTRL and right click upon the selected area. Done.

This goes in the module of ThisWorkbook:

Option Explicit

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    AdjustRowHeight Target

End Sub

This goes in a standard module:

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
    Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If

Const VK_LCONTROL = &HA2

Public Function IsLeftControlPressed() As Boolean
    If GetKeyState(VK_LCONTROL) < 0 Then
        IsLeftControlPressed = True
    Else
        IsLeftControlPressed = False
    End If
End Function

Public Sub AdjustRowHeight(ByVal argTarget As Range)
    Dim c As Range, h As Long
    If IsLeftControlPressed Then
        For Each c In argTarget.Areas
            If c.CountLarge = 1 Then
                h = c.RowHeight
                Exit For
            End If
        Next c
        argTarget.RowHeight = h
    End If
End Sub

 

[JenniferMurphy]

Might have an alternative that suits.
Select a single cell which height will become the reference height. Then select (using CTRL and SHIFT) the cells (or entire rows) of which the row height has to be adjusted. Finaly hold down Left CTRL and right click upon the selected area. Done.

Wow! That will take me a minute or two to get my head around. ?

I am amazed and impressed by the VBA skills of some of the participants of this board. Makes me feel like a slacker. ?

And all this because the AAs at m$ft were too lazy or too clueless to just provide for rows what they already did for columns. I think if I had back all the time I have spent over the years doing extra work because of gaps, bugs, and outright screw-ups by the geniuses at m$ft, I would be 20 years younger. ?