Excel – RowHeight with VBA Help!

Topic

New Reply

[WSRyal83]

Good Morning,

Hoping  someone can assist me with an issue I’ve not been able to solve myself? I’m using the attached code (can’t seem to copy direct onto this) for finding the last four rows but would like to resize the row height (22.5) at the same time, does anyone know if this is possible with my existing code?

The problem I have with the last four rows not being static I was hoping I could extend the code lines so it does it all at once.

Appreciate any responses, thanks in advance.

Thanks,

Ryan

• This topic was modified 5 years, 9 months ago by WSRyal83.

Reply | Quote

Replies

Hi Ryan

I’m not sure about your “CB1.Value” in your code. Is this a cell address???

Perhaps you could use this..

Set zCell = Sheet5.Cells(Rows.Count, "D").End(xlUp)

zCell.Offset(2) = "Total (Excluding VAT)"
zCell.Offset(3) = "=SUM(J21:J40)"
zCell.Offset(5) = "Kind Regards"
zCell.Offset(6) = [CB1]

zCell.Offset(2).Resize(5).RowHeight = 22.5

zeddy
Excel Brutal Adjustments Bureau

1 user thanked author for this post.

WSRyal83

Reply | Quote

Hi Zeddy,

Thanks for your reply. I’m getting variable not defined for the zCell for some reason.

 

I will have a play

Reply | Quote

Hi Zeddy,

Thank you for taking the time to have a look for me really appreciate you help.

I got it working with adding Dim zCell As Range

Thanks,

Ryan

2 users thanked author for this post.

zeddy, Kirsty

Reply | Quote

Hi Ryan

Aha! So you have Option Explicit at the top of your code module.

This means that you must declare all variables (with the Dim statement) before you can use them in your vba routine i.e. every variable you plan to use in your code must be properly declared first via a Dim statement.

This setting is in the VB Editor..
( Tools > Options > [Editor] > [ ]Require Variable Declaration )

Having Option Explicit is useful if you are a bad typist who often misspells your variables e.g. you type myNme instead of myName etc etc etc. You will get a message of Variable not defined whenever vba sees a variable that hasn’t been specifically declared first with a Dim statement. You must also declare your variables correctly of course. No good declaring r as Integer for example if you are planning to use it as a row number, since row number might be over a million – you’ll need to Dim r As Long etc etc etc.

If you declare your variables, code is supposed to run faster and computer memory is allocated more efficiently. This would have been crucial for moon landing programs when your processing power and core memory was really really limited ( and very very expensive).

Personally, I hate having to declare all my variables before I use them in code. It’s like listing all the words you are planning to use before you actually write a book. You just need to be careful with your typing. If you don’t use Option Explicit, Microsoft vba will “coerce” the relevant data type according to context. That’ll work for me. Unless lives depend on it. Then I’ll switch back to conventional, cautious, vigilant, belt-and-braces fully-documented coding.

I once saw this advice somewhere: “Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. Code for readability.”

zeddy
Excel Corrections Officer

1 user thanked author for this post.

access-mdb

Reply | Quote