Vba listbox.column to range

[pulpo]

Hello,
simple question this time. I populated a listbox with3 columns with a range from my worksheet.
If I want to write just one column back to a range in my worksheet do I need to loop through all values of the desired column?
To use one value, selected or described, is easy but the entire column was something I couldn't to without looping.
Any ideas?

With best regards
pulpo

 

[Domenic]

Let's say that you wanted to transfer the items from the 3rd column of your listbox to your worksheet, starting at A2, you could do it this way . . .

    With Me.ListBox1
        Worksheets("Sheet1").Range("A2").Resize(.ListCount).Value = Application.Index(.List, 0, 3)
    End With

Change the references accordingly.

Hope this helps!

 

[pulpo]

Let's say that you wanted to transfer the items from the 3rd column of your listbox to your worksheet, starting at A2, you could do it this way . . .

    With Me.ListBox1
        Worksheets("Sheet1").Range("A2").Resize(.ListCount).Value = Application.Index(.List, 0, 3)
    End With

Change the references accordingly.

Hope this helps!

Hey Domenic,
that works great except that I have problems when the data comes from a range that is filtered. I will try to figure out what is excactly the reason. If I cannot solve it on my own I will upload an example to show what I mean.

Thank you
pulpo

 

[pulpo]

Hey Domenic,
that works great except that I have problems when the data comes from a range that is filtered. I will try to figure out what is excactly the reason. If I cannot solve it on my own I will upload an example to show what I mean.

Thank you
pulpo

ok, that´s surprisingly more difficult with filtered data. Please see attached pictures.

• 3 columns with 500 values each on the worksheet (pic1)
• filter for column C in worksheet is activated (manually), (pic2)
• button "column" click => userform to show the listbox with all values in 3 columns (working).(pic3)
• userform is closed => transfer the 3rd column of the listbox to column E on the worksheet (not working).(pic4,5)

Now it only transfers one value which seems to be the first one to whatever cell that the filter is used on. I hope that helps to understand what I need.

The code looks like this

Sub userform_initialize()
Call column2
End Sub

Sub column2()

Dim LastRow As Integer

Me.Caption = "column"

With Me.ListBox1
    .ColumnCount = 3
    .ColumnWidths = "50;50;50"

End With

With Worksheets("pos")

LastRow = .UsedRange.Rows.Count

Me.ListBox1.List = .Range("A2:C" & LastRow).Value

End With
End Sub
Private Sub UserForm_QueryClose(cancel As Integer, closemode As Integer)

 With Me.ListBox1
        Worksheets("pos").Range("E2").Resize(.ListCount).Value = Application.Index(.List, 0, 3)
    End With

End Sub

Many thanks

pulpo

 

[Domenic]

When the data is filtered, you'll need to loop through each item in your listbox and transfer them to your worksheet one by one. The following code will first check whether the data is filtered. If so, it loops through each item, one by one, etc. If not, it transfers the items to your worksheet without looping.

Private Sub UserForm_QueryClose(cancel As Integer, closemode As Integer)

    Dim dataWorksheet As Worksheet
    Set dataWorksheet = Worksheets("Pos")

    Dim outputRange As Range
    Set outputRange = dataWorksheet.Range("E2")
   
    With dataWorksheet
   
        If .FilterMode Then
   
            Dim i As Long
            With Me.ListBox1
                For i = 0 To .ListCount - 1
                    outputRange.Offset(i).Value = .List(i, 2)
                Next i
            End With
           
        Else
       
            With Me.ListBox1
                outputRange.Resize(.ListCount).Value = Application.Index(.List, 0, 3)
            End With
       
        End If
       
    End With

End Sub

Hope this helps!

 

[pulpo]

When the data is filtered, you'll need to loop through each item in your listbox and transfer them to your worksheet one by one. The following code will first check whether the data is filtered. If so, it loops through each item, one by one, etc. If not, it transfers the items to your worksheet without looping.

Private Sub UserForm_QueryClose(cancel As Integer, closemode As Integer)

    Dim dataWorksheet As Worksheet
    Set dataWorksheet = Worksheets("Pos")

    Dim outputRange As Range
    Set outputRange = dataWorksheet.Range("E2")
  
    With dataWorksheet
  
        If .FilterMode Then
  
            Dim i As Long
            With Me.ListBox1
                For i = 0 To .ListCount - 1
                    outputRange.Offset(i).Value = .List(i, 2)
                Next i
            End With
          
        Else
      
            With Me.ListBox1
                outputRange.Resize(.ListCount).Value = Application.Index(.List, 0, 3)
            End With
      
        End If
      
    End With

End Sub

Hope this helps!

Hey, thanks. It's looping then ok. I appreciate your time to answer.

Best regard

 

[Domenic]

You're very welcome, cheers!