Can I auto-hide rows or auto-set row height?

I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.

When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?

Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell?

Thanks in anticipation!

This should be possible with VBA. Ensure that Text Wrap is ON for all cells that are to be evaluated.
Now if VLookUp returns a value, then auto-fit the RowHeight, Else hide the entire row.

Thanks for that!

I'm not too familiar with VBA. Perhaps I should post this in the VBA section of the forum.

Originally Posted by jewelsharma

This should be possible with VBA. Ensure that Text Wrap is ON for all cells that are to be evaluated.
Now if VLookUp returns a value, then auto-fit the RowHeight, Else hide the entire row.

That looks like exactly what I need to do!

Any help on how to do this with VBA?

Thanks!

Any help?

I am vaguely familiar with VBA as it applies to a worksheet or workbook, but not as it applies to individual cells.

If a cell's VLOOKUP returns no value, I want to hide that worksheet's row. If it DOES return a value, I want that row's height to be automatically set to accommodate the data.

Thanks!

Any help?

I am vaguely familiar with VBA as it applies to a worksheet or workbook, but not as it applies to individual cells.

If a cell's VLOOKUP returns no value, I want to hide that worksheet's row. If it DOES return a value, I want that row's height to be automatically set to accommodate the data. There will be multiple cells with this functionality in the worksheet receiving the data.

Thanks!

So this will ensure that every row is tall enough to accommodate the VLOOKUP linked data:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.EntireRow.AutoFit
Target.EntireColumn.AutoFit
End If
End Sub

How can I now hide any row whose VLOOKUP result is blank?

Sorry, I haven't logged in for the past few days.
You seem to have the first part figured. Now for hiding the row - if VLookUp formula is unable to find a Match, it returns a #N/A error. We can use this information to hide rows as follows:

Please Login or Register  to view this content.

HTH.

Thanks for that. I'll try it at work tomorrow.

The workbook has almost 100 identical pages using the same concept. Is there a way to not have to put the actual worksheet name in each VBA instance?

Also, the auto-set row height VBA posted above doesn't work with rows that have merged cells. Any idea why?

Thanks again!

For the 1st part, we can run two For loops, one within the other to perform this for all worksheets of a workbook. However, Auto-Fit does not work for Merged cells. If you require to autofit merged cells, a different approach would be required. For this, please share a sample workbook (with maybe just 3-4 sheets, instead of 100 .

Please find attached two sample workbooks. I'm using HLOOKUP to link data from the Sample Lesson Plans workbook to individual Sample Lesson Plans Shell worksheets. I then copy and link-paste the individual Shell worksheets into a Word document.

I make the new individual Shell worksheets by duplicating an existing worksheet, and changing the name of the worksheet on the tab. The sheet then HLOOKUP links to the appropriate column of the Sample Lesson Plans worksheet.

Now, I want to:

1) Hide any row on any Shell worksheet that doesn’t have corresponding linked data on the Sample Lesson Plans worksheet. The code I have been trying doesn't work when any cells in the row are merged.

2) Automatically set the row height of cells that DO have linked data high enough to accommodate the linked data.

Thanks!

Any reason why Target.EntireRow.AutoFit doesn't work when there are merged cells in the row?

Is there another technique that will work?

Your example workbooks are very helpful. Earlier, it wasn't clear that you were using "IF", "&" and such other formulas in addition to the LookUp formulas.
So here's a possible fix to hide empty rows, or else Auto-fit their row height.

Please Login or Register  to view this content.

You might need to tweak the code slightly, but it should be able to perform what you desire.
Good luck!