skipping columns in an aray

  • Code
    With rngTarget 
        For lcol = 1 To .Columns.count 
            For lrw = 1 To .Rows.count 
                rcArray(lrw, lcol) = .Cells(lrw, lcol).Text 
            Next lrw 
        Next lcol 
    End With


    Hi I am trying to alter the above array to skip certain columns and I got this far, however the output is blank when passing the array to the multi column list box.
    I could alter the order of the columns in the source data if there isn't a work around but I am sure it must be possible.


    Code
    With rngTarget 
        For lcol = 1 To array(1,2,3,5,8)
            For lrw = 1 To .Rows.count 
                rcArray(lrw, lcol) = .Cells(lrw, lcol).Text 
            Next lrw 
        Next lcol 
    End With



    Cheers


    Dave

  • Re: skipping columns in an aray


    Possibly...


    Typed freehand, untested.

  • Re: skipping columns in an aray


    Hi, I gave it a go and I can see that the filter is working and the list box vertical scroll bar is altering but the output into the multi column list box is blank.
    Full sub here


    Dave

  • Re: skipping columns in an aray


    I notice you're using 1 as the lower bound of the array rather than the default 0. The ListIndex property (The number 'assigned' to each item) of a Listbox is 0 based...


    First step, comment the 'On Error Resume Next' and see what happens,


    You shouldn't turn off error handing while developing (You shouldn't turn it off at all unless you are going to handle specific errors yourself) - it covers a lot of evils that will make themselves known at the worst possible time, like when you think you're finished and are demo'ing to anyone above your pay grade :)


    If the issue is still not obvious, copy of the workbook please - just a sample of anonymised data needed.


    [sw]*[/sw]

  • Re: skipping columns in an aray


    Then it's related to the Array lower bound as I mentioned...


    Try (again, untested. Need a copy of the workbook if this fails).

    Code
    Redim Preserve rcArray(0 To rngTarget.Rows.count - 1, 0 To rngTarget.Columns.count - 1)
  • Re: skipping columns in an aray


    Code
    ReDim Preserve rcArray(0 To rngTarget.Rows.count, 0 To rngTarget.Columns.count)


    Hi, the above worked (just removed the -1s)


    Many thanks as ever.


    Dave

  • Re: skipping columns in an aray


    I can't see how it would work correctly.


    For example, if rngTarget contains 5 rows then looping from 0 to 5 will actually process 6 rows (0,1,2,3,4,5 - and I don't mean to say those are row numbers, but are a reference to the array element).


    Still, if it works...

  • Re: skipping columns in an aray


    Hi, it stopped working until I put the -1s back in but now I am getting an overflow error.


    Dave


    PS sorry if this should be a new thread.

Participate now!

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