Receiving error codes with using user form to rearrange and remove columns

  • I am just a dynamic userform... so, want it to be able to
    remove columns/rearrange them
    filter by criteria with checkboxes, then have the option to filter by date ranges for those columns that have dates, etc.
    Option to build your own pivot table with the area for them to choose which filters to add where.


    I have a lot of it well under way.. but ive hit a road block at the column deleting/sorting. The form I am having trouble with the code is the one titled "removereorder"
    It is bringing over the correct columns but it is only bringing over the header...


    any help or suggestions would be very much appreciate!



    forum.ozgrid.com/index.php?attachment/68994/

  • Re: Receiving error codes with using user form to rearrange and remove columns


    I sent the workbook - if you press control d to open the userform, then click on "open column editing tool", then move some columns, then press filter


    ***Note - the only tab that can be on the workbook is sheet1 and then results will go into sheet 2, which is created at the beginning of this particular sub

  • Re: Receiving error codes with using user form to rearrange and remove columns


    its not that its an error - its that it is not copying the entire column.. its copying row 1 only... also, there is an 'error on resume next' line in there because for some reason autofilter doesn't like tables... it ends up converting it back to a range on its own so I just added a line to remove the table at the beginning.. and the on error resume next in case its not a table yet.

  • Re: Receiving error codes with using user form to rearrange and remove columns


    Well, my original thought was to also have the functionality to both filter out criteria as well as rearrange/remove columns (then export to sheet2) but I couldnt figure out how to do both at once... so i guess my question is do you know how to do that? And if not, what do I need to change to just rearrange/move only the selected columns? Below is the full code for that userform... (minus the trigger in the module..)


  • Re: Receiving error codes with using user form to rearrange and remove columns


    ***Note*** I am not using the sub named "Rando" I was trying to use some code so that it would by dynamic instead of listing the "LB1.AddItem (Sheet1.Range("a1"))" , b1, c1, etc code.... but also couldn't get that working...

  • Re: Receiving error codes with using user form to rearrange and remove columns


    What exactly are you trying to do?


    Are you just trying to copy a selected no of columns, and all the data in those columns, to a new worksheet?


    Or do you actually want to filter data?


    If it's the latter where would the criteria for the filter come from?

  • Re: Receiving error codes with using user form to rearrange and remove columns


    Ideally yes, I would want an option to filter the data but if no filters are selected, then it would move everything.


    The filters would be
    1.) - Duplicate text one column (so, just the same if you were to filter a table) --- i was thinking this would be a check box field
    2.) - date range for the columns that only contain dates (there are never any columns that have a mix of dates and text) --- was thinking this would be 2 fields for the date range
    3.) - Blank or not Blank


    So, my original plan was to have


    1.) list of all fields (tier 1) --- you would select which ones you wanted to move and then in "tier 2" of the report you would have 3 separate choices of where to add the item from tier 1. Those choices would be the 3 filtering options I listed above
    -
    2.) 3 separate areas to add those fields to, with 3 separate add/remove areas because of the 3 different filter options (this would be what i am referencing as tier 2)
    - from there you would filter accordingly and again select "add" which would move the data to the final area prior to selecting "Go" or something where it creates the new sheet. (I would imagine all of the filtering could be done in the background as the user is making their choices.. then the "Go" button would just be moving all visible cells...


    3.) tier 3 would be the final selection... which is the option to remove columns or rearrange the column order



    I am not even sure if this is doable but it made sense to me because of like I said, the filtering as you go in the background..

  • Re: Receiving error codes with using user form to rearrange and remove columns


    I guess the tier 3 would just be just moving all visible cells from 1 column, one at a time, in the order of the selection..

  • Re: Receiving error codes with using user form to rearrange and remove columns


    just thought about it and I guess it would actually be 4 tiers... first tier would be a list of all of the columns (not sure if I mentioned this but it needs to be dynamic
    in the sense the number of columns will vary.. and the date fields, etc. would also vary from column to column depending on the data in the spreadsheet...


    Tier1 - All Columns (Individual Selections to choose how to filter)



    Add / Remove Box Add/Remove Add/Remove
    Tier2 - Select which filter items (duplicates) you want (Checkbox) / Date1 (Text Field) Date2 (Text Field) / Blank or Not Blank (Select which)




    Tier 3 Add Add Add
    Table would be filtered in the background at this point and the selections would be added here




    Tier 4 Choose the order of the columns (Move up, down?)
    Remove any columns you do not want




    Send to Spreadsheet Select "Go" and create new worksheet with the
    then clear original table filtered / arrange / visible cells data
    filters

Participate now!

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