Having next open row unhide automatically

[andrewb90]

Hello all,

I've seen something like this in other sheets, but I'm not sure of what its called or where to find the info, so any tips would be greatly appreciated.
I have a range (A6:V23). To start, every row would be blank, now, once data is entered in A6 then row 7 would appear allowing for data entry, and so on. so instead of having all rows visible, only rows with data in the A column is visible, plus the next empty row (or a row in the middle if data got deleted)

Any help would be very much appreciated.

 

[Fluff]

Hia
Is this what you're after?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Vrw As Long
    Dim Lrw As Long

    If Not Target.Column = 1 Then Exit Sub
    Cells.EntireRow.Hidden = False
    If Range("A6") = "" Then
        Vrw = 6
    Else
        Vrw = Range("A5").End(xlDown).Offset(1).Row
    End If
    Lrw = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    If Vrw = Lrw Then
        Range("A" & Vrw + 1 & ":A" & Rows.Count).EntireRow.Hidden = True
    Else
        Range("A" & Vrw + 2 & ":A" & Lrw + 2).SpecialCells(xlBlanks).EntireRow.Hidden = True
        Range("A" & Lrw + 1 & ":A" & Rows.Count).EntireRow.Hidden = True
    End If

End Sub

It needs to go in the sheet module, rather than a standard module

 

[andrewb90]

I put it in, and the entire sheet below row 7 disappeared. I had data in the first two rows, and I couldn't make it reappear without deleting the code and manually unhiding.
I also only need the empty rows in my range to be hidden (with the exception of the first blank row it comes to) not the remainder of the sheet, as I have data in rows outside the range that needs to be visible.

 

[Fluff]

Missed the bit about the range
Try

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Vrw As Long

    If Not Target.Column = 1 Then Exit Sub
    Cells.EntireRow.Hidden = False
    If Range("A6") = "" Then
        Vrw = 6
    Else
        Vrw = Range("A5").End(xlDown).Offset(1).Row
    End If
    If Vrw > 23 Then Exit Sub
    On Error Resume Next
    Range("A" & Vrw + 1 & ":A23").SpecialCells(xlBlanks).EntireRow.Hidden = True

End Sub

 

[andrewb90]

So that's improved, but It's not quite working the way I want. In that range, I need the first row that is blank to also be visible to allow for a new line of data to be entered.(meaning the blank rows won't always be the last rows, sometimes data is cleared from existing rows, and we want to add new data to the now blank row) Then with that, a new blank line should be revealed so that there is always one row available for data entry.

 

[Fluff]

Could you please show a sample of your sheet, or post your file to somewhere like dropbox, 1drive etc.
As I understand you the code is doing what you need.
eg
Rows6 to 10 are visible as is 11 which is blank.
As soon as you type something into A11 Row 12 becomes visible.
If you then clear row 8, that remains visible, but row 12 becomes hidden as row 8 is the first blank

 

[andrewb90]

What you're describing is exactly what I want. But the blank row isn't showing up for me. If I manually unhide 1 blank row within the range, as soon as I click, it will hide again leaving me no blank rows.

 

[Fluff]

Can you share your file, via dropbox or similar? As I cannot replicate your problems

 

[andrewb90]

So I put the code in 3 separate workbooks, and 1 time it worked, the other two it didn't. Not sure why it is.
Thank you for the help though, I'll keep playing with things to see if I can figure it out.

 

[andrewb90]

I've been playing with it for a while, and here's what I've found:

at random times, when you fill in a row, it won't add a new row.
When you delete items it will always remove the extra row, even if there are no more blank rows (being that the row you cleared, now becomes the only blank, it still disappears)

I tried adjusting the blank rows to 2. Two rows now popup, but again sometimes only one becomes available, then after using the one row, 0 rows will appear.
Then occasionally, when I delete data instead of just one row hiding, two will hide leaving me with only 1 despite the setting being on two

Range("A" & Vrw + [COLOR=#ff0000]2[/COLOR] & ":A23").SpecialCells(xlBlanks).EntireRow.Hidden = True

I'm testing this on a blank spreadsheet, with just typing/clearing random values into A6: A23. I put borders on my entire range just so I could see what was being hidden, but other than that, nothing else is in the sheet.

Fluff, or anybody else: can you replicate what I'm getting while playing with the cells for several minutes?