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

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
ExpExchHelpAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
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
 
ExpExchHelpAuthor Commented:
Rgonzo1971:

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

Thank you a great deal for your help.  

EEH
0
 
ExpExchHelpAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.