Form Created - VBA Dropdown Doesnt Work When The Rows Are Hidden?

Hi,

I have created a "form" where you can put information into a number of fields, hit a "Submit" button and the information stacks up as a list below this form, I use the following code and works fine -

Sub SUBMIT()
Dim NextRow As Long
Application.ScreenUpdating = False
NextRow = Sheets(1).Cells(Rows.Count, "C").End(xlUp).Row + 1
NextRow = Sheets(1).Cells(Rows.Count, "F").End(xlUp).Row + 1
NextRow = Sheets(1).Cells(Rows.Count, "G").End(xlUp).Row + 1
NextRow = Sheets(1).Cells(Rows.Count, "H").End(xlUp).Row + 1
NextRow = Sheets(1).Cells(Rows.Count, "J").End(xlUp).Row + 1
NextRow = Sheets(1).Cells(Rows.Count, "K").End(xlUp).Row + 1
NextRow = Sheets(1).Cells(Rows.Count, "M").End(xlUp).Row + 1
NextRow = Sheets(1).Cells(Rows.Count, "O").End(xlUp).Row + 1
If NextRow < 37 Then
NextRow = 37
End If
Sheets(1).Cells(NextRow, "C") = Sheets(1).Range("J9:O9").Value
Sheets(1).Cells(NextRow, "F") = Sheets(1).Range("J11:O11").Value
Sheets(1).Cells(NextRow, "G") = Sheets(1).Range("J13:O13").Value
Sheets(1).Cells(NextRow, "H") = Sheets(1).Range("J15:K15").Value
Sheets(1).Cells(NextRow, "J") = Sheets(1).Range("M15").Value
Sheets(1).Cells(NextRow, "K") = Sheets(1).Range("O15").Value
Sheets(1).Cells(NextRow, "M") = Sheets(1).Range("J17:O17").Value
Sheets(1).Cells(NextRow, "O") = Sheets(1).Range("C20:O26").Value
Sheets(1).Range("J9:O9").ClearContents
Sheets(1).Range("J11:O11").ClearContents
Sheets(1).Range("J13:O13").ClearContents
Sheets(1).Range("J15:K15").ClearContents
Sheets(1).Range("M15").ClearContents
Sheets(1).Range("O15").ClearContents
Sheets(1).Range("J17:O17").ClearContents
Sheets(1).Range("C20:O26").ClearContents
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Submitted"
ActiveWorkbook.Save
End Sub

The list it stacks into I want to hide, however when I hide the rows where the list is, the 'stacking' doesnt seem to work anymore and only puts the information into row 37 each time and overwrites itself?

Any ideas?

Thanks

Assuming that they're merged cells, try this:

Sub SUBMIT() 'npsnps
Dim NextRow As Long, Col As String, Rng As String
 
Col = "C": Rng = "J9": GoSub GetNext
Col = "F": Rng = "J11": GoSub GetNext
Col = "G": Rng = "J13": GoSub GetNext
Col = "H": Rng = "J15": GoSub GetNext
Col = "J": Rng = "M15": GoSub GetNext
Col = "K": Rng = "O15": GoSub GetNext
Col = "M": Rng = "J17": GoSub GetNext
Col = "O": Rng = "C20": GoSub GetNext

MsgBox "Submitted"
ActiveWorkbook.Save
                    Exit Sub
GetNext:
NextRow = Application.MAX(37, Sheets(1).Cells(Rows.count, Col).End(xlUp).Row + 1)
Sheets(1).Cells(NextRow, Col) = Sheets(1).Range(Rng).Value
Sheets(1).Range(Rng).ClearContents
Return
End Sub

Uno Mas:

Sub SUBMIT() 'npsnps
Dim NextRow As Long, Col As String, Rng As String
 
Col = "C": Rng = "J9": GoSub GetNext
Col = "F": Rng = "J11": GoSub GetNext
Col = "G": Rng = "J13": GoSub GetNext
Col = "H": Rng = "J15": GoSub GetNext
Col = "J": Rng = "M15": GoSub GetNext
Col = "K": Rng = "O15": GoSub GetNext
Col = "M": Rng = "J17": GoSub GetNext
Col = "O": Rng = "C20": GoSub GetNext

MsgBox "Submitted"
ActiveWorkbook.Save
                    Exit Sub
GetNext: NextRow = 37
            With Sheets(1)
Do Until .Cells(NextRow, Col) = "": NextRow = NextRow + 1: Loop
.Cells(NextRow, Col) = .Range(Rng).Value
.Range(Rng).Value = ""
            End With: Return
End Sub

Try this:

Sub SUBMIT() 'npsnps
Dim NextRow As Long, Col As String, Rng As String
 
Col = "C": Rng = "J9": GoSub GetNext
Col = "F": Rng = "J11": GoSub GetNext
Col = "G": Rng = "J13": GoSub GetNext
Col = "H": Rng = "J15": GoSub GetNext
Col = "J": Rng = "M15": GoSub GetNext
Col = "K": Rng = "O15": GoSub GetNext
Col = "M": Rng = "J17": GoSub GetNext
Col = "O": Rng = "C20": GoSub GetNext

MsgBox "Submitted"
ActiveWorkbook.Save
                    Exit Sub
GetNext: NextRow = 37
            With Sheets(1)
Do Until .Cells(NextRow, Col) = "": NextRow = NextRow + 1: Loop
.Cells(NextRow, Col) = .Range(Rng).Value
.Range(Rng).ClearContents
            End With: Return
End Sub

You're welcome and thanks for the rep!