Copy a selected row in a listbox and fill a column to last row

AndyJM87

New Member
Joined
Aug 31, 2018
Messages
26
I will try and explain this as best as I can...

From a 2 column Listbox on a userform, I need to select a row and copy that row into a spreadsheet (columns C and D, from row 5 up to last row).

I have attached some images so you have an idea of what I am trying to do.

The code below will only change the last row... So C7 and D7. I need it to change the whole range up to last row.

Code:
Sub UpdateVRN()

Dim ws As Worksheet
Dim i As Long
Dim lRow As Long

Set ws = ThisWorkbook.Sheets("Data")

        lRow = ws.Range("B" & ws.Rows.Count).End(xlUp).row

    For i = 0 To frmChangeVRN.lstVRN.ListCount - 1

        If frmChangeVRN.lstVRN.Selected(i) Then
            ws.Cells(lRow, 4) = frmChangeVRN.lstVRN.List(i, 0)
            ws.Cells(lRow, 3) = frmChangeVRN.lstVRN.List(i, 1)
        End If
    
    Next i
    
End Sub



 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
If frmChangeVRN.lstVRN.Selected(i) Then
    Ws.Range("D5:D" & Lrow) = frmChangeVRN.lstVRN.List(i, 0)
    Ws.Range("C5:C" & Lrow) = frmChangeVRN.lstVRN.List(i, 1)
End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,336
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top