PDA

View Full Version : Solved: Display 1 empty row only



av8tordude
08-18-2012, 03:50 AM
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
08-18-2012, 04:10 AM
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
08-18-2012, 04:17 AM
Thank you XLD. Works great!

av8tordude
08-18-2012, 04:22 AM
How can I edit the code to only look at the cells in column A?

Bob Phillips
08-18-2012, 04:27 AM
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
08-18-2012, 04:38 AM
Awesome! Thank you XLD

av8tordude
08-18-2012, 06:15 AM
One more favor, please...

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

Bob Phillips
08-18-2012, 08:22 AM
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
08-18-2012, 08:25 AM
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
08-18-2012, 02:15 PM
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