Solved: Display 1 empty row only

[av8tordude]

Can someone assist with a code with the following...

column A, Rows 9 - 100 will be use for data entry, with Row 10-100 being hidden, if A9 is empty. When the user enter information into A9, the next row (row 10), is unhidden. If the user deletes data entered into A9, the row is sorted, but I only want one empty row displayed, leaving the other rows hidden.

The code below allows me to accomplish part of the above, but it does not take into account sorting, leaving only 1 row for data entry.

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell > "" Then
ActiveCell.Offset(1, 0).EntireRow.Hidden = False
Else
ActiveCell.Offset(1, 0).EntireRow.Hidden = True
End If
End Sub

[Bob Phillips]

Including the sort

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

On Error GoTo ws_exit

Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Value <> "" Then
Me.Rows(Target.Row + 1).Hidden = False
Else
Me.Rows("9:100").Sort key1:=Me.Range("A9"), order1:=xlAscending, Header:=xlNo
For i = 10 To 100
Me.Rows(i).Hidden = Me.Cells(i - 1, "A").Value = ""
Next i
End If

ws_exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[av8tordude]

Thank you XLD. Works great!

[av8tordude]

How can I edit the code to only look at the cells in column A?

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

On Error GoTo ws_exit

Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Column = 1 Then
If Target.Value <> "" Then
Me.Rows(Target.Row + 1).Hidden = False
Else
Me.Rows("9:100").Sort key1:=Me.Range("A9"), order1:=xlAscending, Header:=xlNo
For i = 10 To 100
Me.Rows(i).Hidden = Me.Cells(i - 1, "A").Value = ""
Next i
End If
End If

ws_exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[av8tordude]

Awesome! Thank you XLD

[av8tordude]

One more favor, please...

How can I edit the code so I can use it on a userform button?

[Bob Phillips]

Public Sub HideRows()
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Sheet1")

.Rows("9:100").Sort key1:=.Range("A9"), order1:=xlAscending, Header:=xlNo
For i = 10 To 100

.Rows(i).Hidden = .Cells(i - 1, "A").Value = ""
Next i
End If

Application.ScreenUpdating = True
End Sub

[av8tordude]

xld...thank you

The code seems to lag quite a bit when I changed the row numbers to 9 - 10009. Is there a way to speed it up?

[av8tordude]

Public Sub HideRows()
Dim i As Long

Application.ScreenUpdating = False

Dim rng As Range
With Range("A10").Resize(10000)
.EntireRow.Hidden = False
For Each rng In .Offset(-1).SpecialCells(4).Areas
rng.Offset(1).EntireRow.Hidden = True
Next
End With

Application.ScreenUpdating = True
End Sub