Remove a entire column inside a listbox

    Hi there!


    I have a multi column listbox (9 columns) in a userform.


    I'm trying to create a checkbox which remove a entire column of the listbox when marked (if unmarked, the column comes back), so I'll able to export the listbox contents without the column related to that checkbox.


    How do I remove a listbox column using a control like a checkbox?


    Notes:


    It is already populated and I already have a button which exports and save the listbox content into a new workbook.
    I am not trying to remove a column in a worksheet using a listbox. I just want to remove a column inside a listbox in a userform.
    I already tried to hide the column setting its width to 0, but it still appears in the exported file.


    Thank you very much!

    Re: Remove a entire column inside a listbox


    Welcome to the forum.


    Is the column the first or last one in the listbox, or is it in between other columns?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Remove a entire column inside a listbox


    You could use something like below to remove a column in the result , using the "Index" function
    Checkbox = True = 3 columns
    Checkbox= False = 4 columns

    Code
    Dim Ray, nRay As Variant
Ray = ListBox1.List
If CheckBox1.Value = True Then
    nRay = Application.Index(Ray, Evaluate("Row(1:" & UBound(Ray, 1) & ")"), Array(1, 3, 4)) '<<see in Array, columns 2 is missing
    Range("N1").Resize(UBound(Ray, 1), UBound(Ray, 2)).Value = nRay
Else
    Range("N1").Resize(UBound(Ray, 1), UBound(Ray, 2) + 1).Value = Ray
End If

    Re: Remove a entire column inside a listbox


    Thank you very much MickG! It worked well!


    I'll use it with the hide code, so when the user selects the checkbox, the column width becomes 0, and the column in the result will be removed with the option you presented.


    rory, i send you a reply before, but I'm not sure if you saw it, so I'll repeat that here, ok? The column is variable. Each column have a checkbox related to it, so when the user select it, the column disappear. I think the thread is closed, but thank you by your intention to help.


    So guys, thank you! Have a nice week. :)


    Best Regards,
    Lehiannel

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login