You are here:

Excel/Macro/VBA for Row Height(s)

Advertisement


Question
QUESTION: Hello,

I would like a macro created that will adjust a given Row's height (in Inches) based upon the Value entered in a given cell.

Example:  I enter a Value of 1.3 in cell A1 and it will then automatically adjust the Height of Row A to 1.3".

The Cell and Row designations above are purely an example. I want to ultimately be able to apply this Macro to the Cells and Rows of my choosing.

Thank you!

ANSWER: Ken

Assume cell A1 is where you will enter you height in inches and Cell D2 is a cell in the row where you want to change the row height.

Right click on the sheet tab of that sheet and choose view code.

Paste in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
 If IsNumeric(Target.Value) Then
       Range("D2").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)
 End If
End If
End Sub

then hit Alt+F11 to get back to Excel and enter a value in A1.  Macros must be enabled.

Tested and worked for me.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

Replication Error
Replication Error  
QUESTION: Thank you so much!  That definitely works, and I didn't know anything about Macros and VBA functions until yesterday. I did a ton of research but was lost.

One issue I found, is trying to duplicate that VBA/Macro multiple times in the same sheet. I tried to simply paste another of your templates below the first and edit the Cell and Row entries, but to no avail. Is it possible to use this multiple times in the same sheet?  I need to replicate it at least 8 times. There's nothing amazing riding on this, it's more of a part of a fun project. So, if you have more pressing items please tend to those first.

Either way, I can't thank you enough for your kindness and quick answer. I really can't believe this is free!  I pledge, regardless of being able to solve the duplication issue, I'll be providing extremely postive feedback.

I did take a couple screen shots of the problem and will try to send them with this reply.

Thank you so much, Tom!  You are absolutely amazing!

Regards,
Ken

Answer
Ken,

You picture is a little to small to see the details, but I understand the problem.  You can only have one change event in a sheet module.  However, you can put multiple conditions in that single change event.  

Here is an example

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
 If IsNumeric(Target.Value) Then
       Range("D2").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)
 End If
End If

If Target.Address = "$C$11" Then
 If IsNumeric(Target.Value) Then
       Range("R2").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)
 End If
End If

If Target.Address = "$F$9" Then
 If IsNumeric(Target.Value) Then
       Range("X2").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)
 End If
End If

End Sub

so when the event is fired by making a change in a cell, the event will process through the code looking for the trigger cell address to match one of the Target.Address addresses.  If it finds one, then it will execute the code in that block.

I believe that is the information you need to achieve your goal.

Thanks for the positive comments.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.