Unhide the next empty row and copy list box value

[adam]

HI anyone,
I'm trying to unhide the next empty row and copy the list box value to column E with the following code. My code does unhide the rows but it does not copy the values. How could I overcome this?

Any help on this would be kindly appreciated.

Code: Select all

Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim r           As Long
    Dim C           As String
    
    For i = 18 To 53
        If Range("A" & i).EntireRow.Hidden = True Then
            If Range("A" & i).Value = "" Then
                Range("A" & i).EntireRow.Hidden = False
                Exit Sub
            End If
        End If
    Next

    If Range(E & 18) = "" Then
        r = 18
    Else
        r = Range(E & 17).End(xlDown).Row + 1
    End If
    Range(E & r) = Me.Listbox1.Column(0)
End Sub

[HansV]

Change E to "E"

[HansV]

Do you want to copy to the row that has just been unhidden? If so:

Code: Select all

Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Long
    
    For i = 18 To 53
        If Range("A" & i).EntireRow.Hidden = True Then
            If Range("A" & i).Value = "" Then
                Range("A" & i).EntireRow.Hidden = False
                Range("E" & i) = Me.Listbox1.Column(0)
                Exit Sub
            End If
        End If
    Next i
End Sub

[adam]

Thankyou verymuch Hans. It worked as needed.