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

Insert_Key

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
Try this in the worksheet module:

VBA Code:
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
 
Solution

Insert_Key

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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:
Excel Formula:
=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:
Excel 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

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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:

Excel Formula:
=OFFSET('Helper Sheet'!$H$1,1,0,MAX(IF('Helper Sheet'!D:D=C2,'Helper Sheet'!E:E)),1)
with
Excel Formula:
=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 :)
 

Forum statistics

Threads
1,147,633
Messages
5,742,235
Members
423,716
Latest member
melpotter22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top