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.

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!

Thanks for that!

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

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:

With ThisWorkbook.Worksheet("Sheet1")         'Replace Sheet1 with the actual worksheet name.
For x = 1 To 100 Step 1                       'Replace 1 and 100 as applicable to your case
If Application.IsNA(.Range("B" & x)) Then  .Range("B" & x).EntireRow.Hidden = True     'Replace B with the VLOOKUP column 
Next x
End With

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.

Dim ws as worksheet
For Each ws in ThisWorkbook                 'this would run the loop for all worksheets
With ThisWorkbook.ws                         
For x = 6 To 57 Step 1                         'Replace 6 and 57 with the starting row and last row [I've chosen them based on the TR-A worksheet]
If .Range("A" & x) = "" Then  
.Range("A" & x).EntireRow.Hidden = True     'So if the cell is blank, the row would be auto-hidden, irrespective of whether it has merged cells or not
Else                                                   'The row is not blank and needs to be auto-fit.
Call Row_Autofit(ws.name, x)
End If
Next x                                             'Go to Next Row
End With
Next                                               'Go to next worksheet

'The following procedure is for auto-fitting rows
Sub Row_Autofit(sWs as String, iRowNum As Integer)
Dim sMerged_Range As String, sStart_Cell As String
Dim dStart_Cell_Width As Double, dMerged_Range_Width As Double, dStart_Cell_ColWidth As Double
Dim iMergeLastCol As Integer, rMerge As Range

With ThisWorkbook.Worksheets(sWs)
            
          If IsNull(.Rows(x).MergeCells) = True Then                             'checks whether or not the row has any merged cells
                    If .Cells(x, 1).MergeCells = True Then
                                   Set rMerge = .Cells(x, 1).MergeArea
                                   iMergeLastCol = 24
                                   sMerged_Range = "A" & x & ":X" & x
                                   sStart_Cell = "A" & x
                                   dMerged_Range_Width = .Range(sMerged_Range).Width
                                   dStart_Cell_Width = .Range(sStart_Cell).Width
                                   dStart_Cell_ColWidth = .Range(sStart_Cell).ColumnWidth
                                   .Range(sMerged_Range).UnMerge
                                   .Range(sStart_Cell).ColumnWidth = dStart_Cell_ColWidth * dMerged_Range_Width / dStart_Cell_Width
                                   .Range(sStart_Cell).Rows.AutoFit
                                   .Range(sMerged_Range).Merge
                                   .Range(sStart_Cell).ColumnWidth = dStart_Cell_ColWidth

                                   Set rMerge = Nothing
                              End If
               Else
                    .Rows(x).AutoFit
               End If     
     End With
End Sub

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