Clear cell and resize all row heights to fit cell contents with VBA (total noob)

[Insert_Key]

Hi there. I am seeking some help to automate a process in Excel.

I suspect it would be a piece of cake in VBA, but don’t know the first thing about VBA – tried to fill in some blanks through Google but didn’t get very far, so here I am! ??

When a user selects a value from a dropdown list, I would like (1) all of the rows in the active worksheet to resize (height) to fit the contents of their cells, and also (2) clear the contents of a specific cell. The cell that has just been cleared contains a second dropdown list and when the user makes a selection from it, (3) all rows on the active sheet need to resize to fit again.

The workbook has four worksheets. For simplicity, let’s call them “Sheet A”, “Sheet B” and “Sheet C”, plus a hidden helper sheet called “Sheet H”. Word Wrap is on.

• The data that I want resized is on Sheet C
• Sheet C is essentially a user-friendly read-only view of information on Sheet B
• ‘Sheet C’!B2:B26 contains descriptions (text only)
• ‘Sheet C’!C2:C26 contain VLOOKUPs, plus two cells with drop down lists (Data Validation)
• The Data Validation Lists are in cells ‘Sheet C’!C2 (let’s call this Product ID) and ‘Sheet C’!C14 (let’s call this one Part ID)
• Selecting an item from each Data Validation List populates other cells through VLOOKUP queries, returning related data from Sheet B

There are two processes that I undertake to complete the activity manually:

1. When an Product ID is selected, clear the Part ID and resize all row heights:
   1. Click in ‘Sheet C’!C2
   2. Select the desired Product ID from dropdown list
   3. Clear contents of ‘Sheet C’!C14
   4. Select all
   5. Resize row height to fit contents
2. When a Part ID is selected, resize all row heights:
   1. Click in ‘Sheet C’!C14
   2. Select the desired Part ID from dropdown list
   3. Select all
   4. Resize row height to fit contents

I believe that there will be two events to trigger the process in VBA: the change to value in C2 (step 1.2 above), and similarly change in C14 (step 2.2) but to say I have no idea how to even start to approach this in VBA is an understatement. I am interested in learning it though and have signed up this afternoon to an online course delivered through Udemy.

There is currently only data in cells B2:C26. The number of rows may change in the future and I use select all as I find convenient when completing my manual process, however if it is likely to introduce issues (e.g. with performance), I am happy to use the range provided above and adjust if/when required.

Any help will be extremely gratefully received! ?

Thanks in advance,

Andy

 

[steve the fish]

Try this in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 Then
    Select Case Target.Address(0, 0)
        Case "C2"
            Application.EnableEvents = False
            Range("C14") = ""
            Rows("3:13").EntireRow.AutoFit
            Application.EnableEvents = True
        Case "C14"
            Rows("15:26").EntireRow.AutoFit
    End Select
End If

End Sub

 

[Insert_Key]

Thank you so much! ?

That works great but for one issue - each time the worksheet is closed and reopened, the Data Validation in C14 seems 'inactive' and the dropdown list does not present.

Selecting the cell and opening the Data Validation dialogue shows that its formula is still in place, and hitting enter or selecting OK 're-enables' the dropdown list. This only needs doing once after opening the file; the dropdown will continue to work fine until the next time the file is closed and reopened.

Any idea on what the issue is and how I address it?

Again, thanks in advance for your help.

 

[Insert_Key]

A bit of additional detail for clarification: the dropdown list icon/arrow is present and when I click on it there is the briefest flicker of what I think is a progress wheel but no visible change on the sheet.

The list box doesn't seem to appear at all, meaning that the user cannot make their second selection and the information presented by the table is incomplete. I hope this helps.

 

[Insert_Key]

Has anybody got an insight into the issue with the dropdown list described above? Forum rules don't allow me to post about it separately so I need to keep bumping this thread.

Fingers crossed! ?

 

[Insert_Key]

I have tested the code in a dummy workbook, using (a) in-cell Data Validation and also (b) a list entered on another worksheet and it all worked fine, suggesting to me that it's not the code itself but rather something in the content / setup of my actual workbook that the code doesn't like. The formula used in the Data Validation that I'm having problems with is:

=OFFSET('Helper Sheet'!$H$1,1,0,MAX(IF('Helper Sheet'!D:D=C2,'Helper Sheet'!E:E)),1)

'Helper Sheet'!$H$1 contains the following CSE formula:

{=IFERROR(INDEX($F1:$F500, MATCH(0, COUNTIF($H$1:H1, $F1:$F500)+($D1:$D500<>'Single Profile View (Read Only)'!$C$2), 0)),"")}

'Single Profile View (Read Only)' is where the supplied solution lives in my workbook.

The Data Validation works as expected (a) when the VBA code is removed or (b) if the code is retained but the Data Validation dialogue box is opened and "OK" is clicked.

I've played with it for a fair while today but the only thing I've achieved is tired eyes and a headache.

 

[Insert_Key]

Bump ?

 

[Insert_Key]

I have no idea why it works as a solution but in the cell that was causing me problems, I have replaced the following formula used for Data Validation:

=OFFSET('Helper Sheet'!$H$1,1,0,MAX(IF('Helper Sheet'!D:D=C2,'Helper Sheet'!E:E)),1)

with

=OFFSET('Helper Sheet'!$H$1,1,0,COUNTIF('Helper Sheet'!$D:$D,$C$2),1)

and the dropdown list is now active on File Open. Job done