• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to set "Row Height" with a macro?

Eloise T

Active Member
I need to set the row height to 25.00 (32 Pixels) beginning with row 7 and can't seem to get the macro to cooperate.

Please see attachment; particularly the Sub: Sub Change_Font_To_Calibri_11_And_Change_Row_Height_To_25()

Thanks for your help in advance.

Code:
Sub Change_Font_To_Calibri_11_And_Set_Row_Height_To_25()
Dim Ws As Worksheet
    For Each Ws In ThisWorkbook.Worksheets
            If Ws.Cells(Rows.Count, "A").End(xlUp).Row > 6 Then           'Changed 3 to "A" which is the same.
'               Affects <Columns A through J>
'         > > > This VBA segment changes the font and font size in Columns A through J from whatever it happens to
'               be to Calibri 11 starting in Row 7.
                Dim wksFnt As Worksheet
                Dim rngFnt As Range
                    For Each wksFnt In ActiveWorkbook.Worksheets
                        Set rngFnt = Intersect(wksFnt.Range("A7:J" & Rows.Count), wksFnt.UsedRange)
                        If Not rngFnt Is Nothing Then
                            With rngFnt.Font
                                .Size = 11
                                .Name = "Calibri"
'                               .N E E D   C O M M A N D    T O    C H A N G E    R O W   H E I G H T   T O   2 5  ( 3 2   P i x e l s ).
                                Rows("7:257").RowHeight = 25
                            End With
                        End If
                    Next
            End If
    Next
    Beep
End Sub
 

Attachments

  • C h a n d o o - ROW Height macro.xlsm
    258.4 KB · Views: 2
Eloise,​
on my actual tests computer, 25 does not exist as it's 24.75 for 33 pixels or 25.50 for 34 pixels but​
it's not a concern as VBA always chooses the closest value according to the computer setup …​
As the procedure is located in the Sheet1 worksheet module​
so without any sheet reference your actual codeline belongs to Sheet1 !​
For another sheet do not forget to add a sheet reference for each range in the codeline and​
as the codeline has nothin' to do in a With Font block : wksFnt.Rows("7:" & wksFnt.UsedRange.Rows.Count).RowHeight = 25 …​
 
I tried both Marc's and Fluff's remedies, but neither worked. Did I put the lines in the wrong place?

Code:
Sub Change_Font_To_Calibri_11_And_Set_Row_Height_To_15()
Dim Ws As Worksheet
    For Each Ws In ThisWorkbook.Worksheets
            If Ws.Cells(Rows.Count, "A").End(xlUp).Row > 6 Then           'Changed 3 to "A" which is the same.
'               Affects <Columns A through J>
'         > > > This VBA segment changes the font and font size in Columns A through J from whatever it happens to
'               be to Calibri 11 starting in Row 7.  Additionally, it will set row height to 15.00 (20 pixels).
                Dim wksFnt As Worksheet
                Dim rngFnt As Range
                    For Each wksFnt In ActiveWorkbook.Worksheets
                        Set rngFnt = Intersect(wksFnt.Range("A7:J" & Rows.Count), wksFnt.UsedRange)
                        If Not rngFnt Is Nothing Then
                            With rngFnt.Font
                                .Size = 11
                                .Name = "Calibri"
'                               .N E E D   C O M M A N D    T O    C H A N G E    R O W   H E I G H T   T O   15.00  ( 2 0   P i x e l s ).
                                Rows("7:257").RowHeight = 15
                                rngFnt.EntireRow.RowHeight = 15
                                wksFnt.Rows("7:" & wksFnt.UsedRange.Rows.Count).RowHeight = 15
                                .EntireRow.RowHeight = 15
                            End With
                        End If
                    Next
            End If
    Next
    'THREE BEEPS----------------------------------------------------------------------------------------
    Beep
'   Pause a second before engaging the next Beep so they don't run together and sound like only one Beep.
'                                   hrs:mi:secs
    Application.Wait Now + TimeValue("0:00:01")
    Beep
'   Pause a second before engaging the next Beep so they don't run together and sound like only one Beep.
    Application.Wait Now + TimeValue("0:00:01")
    Beep
    
End Sub
 

Attachments

  • C h a n d o o - ROW Height macro.xlsm
    259.2 KB · Views: 2
Eloise T
... above shows that ...
You have tried to adjust something like 'FONTs RowHeight' instead of Rows.
Code:
With rngFnt.Font
' something coding
end with
>>> Move Your adjusting
to that kind of place
where RowHeight effects to Row or Range.
 
Back
Top