VBA - Hide Rows in Excel

Thanks for the reply! Maybe you can help me with a better code?

I want to accomplish the below:
If I type in a certain letter (ex. A) my sheet is going to populate a table (ex. with X's) - see below.

If I type in a certain letter (ex. B) my sheet may or may not populate more rows (ex. with X's) - see below.

I want to hide the yellow (empty) rows each time I do this. So if my table is 100 rows and changing a letter populates 70, I want the extra 30 to automatically be hidden. Basically by changing a letter and data coming in, I want the rest of the rows that don't get touched to automatically hide and then reappear if the letter is changed if more rows are used.

Re: still trying to hide

It seems to me that your code ? to add data to the sheet, first needs to unhide all rows.

Then your code can add data where necessary.

There is separate ShowRows code included below that displays all rows from 1 to 1000.

The code below (except ShowRows) is run whenever cell F6 is changed.
It unhides any hidden rows, checks for blank rows above row 124 and hides them.

You will need to change cell "F6" to the appropriate cell and verify the rest of it.

Best to try it on a test sheet.

NOTE: Application.EnableEvents (the default is true) must be true in order for the code to run. If the code won't run try setting .EnableEvents to True.

(it is set to false while the code runs to prevent run away code)

The code goes in the built-in SHEET Module attached to the sheet of interest.

Again it runs when ever cell F6 is changed.

'---

Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1).Address(False, False) = Me.Range("F6").Address(False, False) Then
Application.EnableEvents = False
Call HideRows
End If
Application.EnableEvents = True
End Sub

Sub HideRows()
Dim StartRow As Long, ColNum As Long, LastRow As Long

Application.ScreenUpdating = False
Me.Rows("1:124").Hidden = False

ColNum = 6
LastRow = 123
StartRow = Me.Cells(LastRow, ColNum).End(xlUp).Row + 1
Me.Range(Me.Cells(StartRow, ColNum), _

              Me.Cells(LastRow, ColNum)).EntireRow.Hidden = True  

Application.ScreenUpdating = True
End Sub
'---

Sub ShowRows()
Me.Rows("1:1000").Hidden = False
End Sub
'---

Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

A list.

Re: "I want the rest of the rows that don't get touched to automatically hide and then reappear"

Is your data a list or a Table?...

Nothing Left To Lose

Re: hiding and showing rows

With your code, once you hide a row, it will stay that way;
unless you have a way to enter data into hidden cells? (i 6).

(maybe you have code to unhide all rows?)

Running your code automatically doesn't seem to me to be a good idea.

'---

Nothing Left To Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)