Hi all

I have a form that I have set up whereby cells D71:I118 are completed automatically based on the selections made from a drop down box.  

Each of the selections in the drop down will auto fill the table in with between 1 and 17 rows of data.  I would like to be able to hide empty rows once the selection has been made.  I have tried some code that allowed me to do this, however when I then changed the selection to one with more rows of data, it did not unhide them again, meaning data was missing from the table.

Can anyone please help?  If you need further information please let me know.

Thanks :)

Re:  dropdown display of rows

Have the initial part of your code always unhide all of the worksheet rows when any selection change is made.
You can then hide the appropriate rows as needed.

'---
Jim Cone
free & commercial excel programs  (n/a xl2013)
http://jmp.sh/K95N3ee

Thanks for the reply.  I've managed to sort it, however it is very slow to run.  Is there a way of speeding it up?

Sub 

Rows("71:118").Select
Selection.EntireRow.Hidden = False

Application.ScreenUpdating = False

Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("D71:I118"), ActiveSheet.UsedRange)
For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
Rng.Item(ix).EntireRow.Hidden = True
End If
Next
done:
Application.ScreenUpdating = True
End Sub

Re:  hiding rows takes too long

Your code hides a row if any cell in the row is blank.
Once a row is hidden, it continues to check cells in the row.

Also, your code does not replace char 160 (hard space) with char 32 (space) or trim the cell.
It only checks if the cell would be blank if the above were done.

I have replaced your sub with two separate subs.
One to clean cells and one to hide rows.
This may or may not suit your situation, but it gives you alternatives...
'---
Sub CleanMeUp()
 'Only needs to be done once.
 'Repeat only after additional data imported into worksheet.
  Dim Rng As Range, ix As Long
  Dim rCell As Range
  Dim vText As Variant
 
  Application.ScreenUpdating = False
 
  Rows("71:118").Hidden = False
  Set Rng = Intersect(Range("D71:I118"), ActiveSheet.UsedRange)
  For Each rCell In Rng.Cells
    vText = rCell.Value
    vText = Trim(Replace(vText, Chr(160), Chr(32)))
    rCell.Value = vText
  Next
done:
  Application.ScreenUpdating = True
End Sub
'---

Sub HideRowsWithAnEmptyCell()
  Dim R As Long
  Dim C  As Long
  Dim Rng As Range
 
  Application.ScreenUpdating = False
  Rows("71:118").Hidden = False
  Set Rng = Intersect(Range("D71:I118"), ActiveSheet.UsedRange).Cells

  For R = Rng.Rows.Count To 1 Step -1
    For C = 1 To Rng.Rows(R).Cells.Count
      If IsEmpty(Rng.Cells(R, C)) Then
        Rng.Rows(R).EntireRow.Hidden = True
        Exit For
      End If
    Next
  Next
ReallyDone:
  Application.ScreenUpdating = True
End Sub
'---

Jim Cone

• Edited by James Cone 12 hours 57 minutes ago