Seeking method to set row height of LastRow

[auto.pilot]

Using Excel 2010, I am seeking a VBA method to set the row height of the LastRow

My last row is defined as ...

 LastRow = Range("B" & Rows.Count).End(xlUp).Row

I know how to paste to the last row, but the syntax or method to set the height escapes me. I've searched and can't find anything that matches this question.

Appreciate any thoughts.

Thanks

Jim

 

[Joe4]

Something like this:

    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    Rows(lastRow).RowHeight = 25

Hint: If you forget the VBA code for things such as this, just use the Macro Recorder and record yourself setting the row height manually to see what that part needs to look like.

 

[auto.pilot]

Perfect. I knew the part about setting the height, but not how to ID the row. Thank you!

 

[Joe4]

You are welcome!

If you recorded yourself manually setting the height of some row, it would record something like:

    Rows("14:14").Select
    Selection.RowHeight = 25

which you can simplify to:

    Rows("14:14").RowHeight = 25

The code that you provide just captures a row number, and stores it in a variable named "lastRow".
So all you have to do is substitute in the name of the variable in place of the hard-coded row numbers.

 

[auto.pilot]

You make it sound so easy... but a novice like me put quotes around the variable (as one of many failed attempts)!

Code like this fails, which is exactly why I am here!

 Rows("LastRow").RowHeight = 25

and

 Rows("LastRow").Select

Thanks!

Jim

 

[Joe4]

I know there are people of many different skill levels here, so I often try to explain what I did to help them learn.

One thing that does often trip up VBA noobs is that anything enclosed in double-quotes is treated as literal text, so you never want to enclose variables in double-quotes.

A good way to illustrate it is to run this little macro which returns two Message Boxes and see what each returns:

Sub Test()


    Dim lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    MsgBox lastRow
    MsgBox "lastRow"
    
End Sub

 

[auto.pilot]

Excellent! I understand the issue without even running the sample code. However, I never made the connection as you have so eloquently described it. Thank you very much!

 

[Joe4]

You are welcome.
Keep on Excel-ling!