?
Solved

Excel -- increase row height through cell entry (via VBA)

Posted on 2016-10-20
3
Medium Priority
?
96 Views
Last Modified: 2016-10-21
Experts:

I am exploring to reorganize a spreadsheet.   Before I do so, I am wondering if row height could be increased based on selection from a drop-down box (via VBA).

Background:  
- Excel's default height of rows = 15.
- I have a spreadsheet that may have 30 rows.
- With row height set to 15, I could easily compare values of, e.g., cell A2 and cell A30.

Challenge:
- Given the values for some cells, the row height may increase to = 120 in order to show all cell content.
- If so, comparing row #2 with row #30 may be tedious and would required scrolling up/down constantly.
- Granted, I could freeze row #2 and then scroll up so that row #30 would appear right below row #2.   However, I believe users may find that too cumbersome as they want to compare all types of data (e.g., compare row #3 with row n; then compare row #10 with row n, and so forth).  Essentially, a user would have to constantly freeze/unfreeze rows to compare them.    This is not ideal.

Here's what I envision:
- Use the default row height = 15.
- In row #1, have two drop-downs in cell A1 and B1.  
- User selects/enters, e.g., "2" in A1 and "30" in B1.  Based on selection of "2" and "30", I now want to increase the height of row #2 and row #30 and make them -- and only them -- equal to = 120 *or* set to "auto height" (via some form of macro/VBA).  
- Upon analysis, user then wants to compare row 3 and row 10.  Upon entering those two values in A1 and B1, rows #2 and #30 would shrink back to height = 15 while row #3 and #10 increase to height equal to 120 (or auto height).

Hopefully that makes sense.   Looking forward to some VBA ideas that would automate the increase/decrease of row heights based on row value selections.

Thank you,
EEH
0
Comment
Question by:ExpExchHelp
  • 2
3 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41853283
Hi,

you can do it by entering a comma separated list of rows in A1
(Dropdowns in Excel sheet do not accept multiple values)

in the worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Range("A2:A" & Rows.Count).RowHeight = 15
    If Target <> "" Then
        aRows = Split(Target, ",")
        For Each rw In aRows
            Rows(rw).AutoFit
        Next
    End If
End If
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
EE20161021.xlsm
0
 

Author Comment

by:ExpExchHelp
ID: 41853608
Rgonzo1971:

Absolutely perfect!   Absolutely perfect, solution!!!  :)

Thank you a great deal for your help.  

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 41853739
Rgonzo1971:

Quick follow-up question...  at this time, the Excel spreadsheet has become very slow.   I actually have hundreds of rows and dozens of columns.  

Question #1:  Instead of calling the function everytime I change some cell value in the worksheet, is there a chance to modify the code will only be called when I actually update cell A1.

Question #2: Also, how could I add command (e.g., command button) -- maybe in B1 -- so that, if needed, I can increase/shrink all of the rows?

Question #3:  Finally, how could I add range of rows (e.g., 5 through 19) so that only those 15 rows will be expanded to the auto height?

Thanks,
EEH
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question