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]

try changing this

Vrw = Range("[COLOR=#ff0000]A6[/COLOR]").End(xlDown).Offset(1).Row

Just realised that A5 on my test sheet wasn't blank & was therfore causing problems

 

[andrewb90]

awesome! that works perfectly thank you!

 

[andrewb90]

Is there a way to set several ranges to all work under the same rules? 6:23, 25:42, etc so there is 1 free line in each section?

 

[andrewb90]

Its one of those times where I am happy to answer my own question, and hopefully make this useful for someone else reading it. Here's the code for 6 different ranges - each leaving 1 blank row.

Private Sub Worksheet_Change(ByVal Target As Range) ' remove copy from mgr    Dim Vrw As Long
    Dim Vrw2 As Long
    Dim Vrw3 As Long
    Dim Vrw4 As Long
    Dim Vrw5 As Long
    Dim Vrw6 As Long
    Application.ScreenUpdating = False
    If Not Target.Column = 3 Then Exit Sub
    Cells.EntireRow.Hidden = False
    
    
    If Range("C6") = "" Then
        Vrw = 6
    Else
        Vrw = Range("C6").End(xlDown).Offset(1).Row
    End If
    
    If Range("C25") = "" Then
        Vrw2 = 25
    Else
        Vrw2 = Range("C25").End(xlDown).Offset(1).Row
    End If


    If Range("C44") = "" Then
        Vrw3 = 44
    Else
        Vrw3 = Range("C44").End(xlDown).Offset(1).Row
    End If
    
    If Range("C63") = "" Then
        Vrw4 = 63
    Else
        Vrw4 = Range("C63").End(xlDown).Offset(1).Row
    End If
    
    If Range("C82") = "" Then
        Vrw5 = 82
    Else
        Vrw5 = Range("C82").End(xlDown).Offset(1).Row
    End If


    If Range("C111") = "" Then
        Vrw6 = 111
    Else
        Vrw6 = Range("C111").End(xlDown).Offset(1).Row
    End If
    
    If Vrw > 124 Then Exit Sub ' make this the last row of all them?
    On Error Resume Next
    Range("C" & Vrw + 1 & ":C23").SpecialCells(xlBlanks).EntireRow.Hidden = True
    Range("C" & Vrw2 + 1 & ":C42").SpecialCells(xlBlanks).EntireRow.Hidden = True
    Range("C" & Vrw3 + 1 & ":C61").SpecialCells(xlBlanks).EntireRow.Hidden = True
    Range("C" & Vrw4 + 1 & ":C80").SpecialCells(xlBlanks).EntireRow.Hidden = True
    Range("C" & Vrw5 + 1 & ":C109").SpecialCells(xlBlanks).EntireRow.Hidden = True
    Range("C" & Vrw6 + 1 & ":C124").SpecialCells(xlBlanks).EntireRow.Hidden = True
    Application.ScreenUpdating = True
    End Sub

 

[Fluff]

awesome! that works perfectly thank you!

Glad to help & thanks for the feedback.

Also glad to see you were able to sort out your other query