+ Reply to Thread
Results 1 to 12 of 12

Select and copy n number of rows from Filtered Table

  1. #1
    Registered User
    Join Date
    10-20-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Select and copy n number of rows from Filtered Table

    Hi,

    I have developed a macro that copies a specific number of rows from a table based on cell H5. However now that I have filtered the table it does not copy the visiable rows but looks at the complete table. How can I change my code below to accomodate this??


    Please Login or Register  to view this content.
    Last edited by Swindo; 10-21-2013 at 12:40 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Select and copy n number of rows from Filtered Table

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    10-20-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Select and copy n number of rows from Filtered Table

    My apologies - This is now done

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Select and copy n number of rows from Filtered Table

    try this instead of regular select

    range(Copyrange).SpecialCells(xlCellTypeVisible).Select
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Select and copy n number of rows from Filtered Table

    Thanks for adding the Code Tags

    Try this…

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-20-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Select and copy n number of rows from Filtered Table

    I must be doing something wrong I amended my code to both suggestions above and it still copies lines from my filtered table that are hidden. Have I done something wrong? I have even tried the below with no luck...... Getting desperate. The code looks right (your example) but it starts from the top and copies both filtered and hidden rows???????

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Select and copy n number of rows from Filtered Table

    this code you just posted is very different to the one you had previously

    there is no mention of h5 anywhere
    copyrange is no longer used

    i am not quite sure what your code does now

  8. #8
    Registered User
    Join Date
    10-20-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Select and copy n number of rows from Filtered Table

    I am a complete novice at this and have been asked by the HR manager to develop a spreadsheet where it lists all staff in the company with the columns for Employee ID, Name, Location Code, LocationDecription and company employed by. From there I have added a excel formula generating a random number for each employee. The HR Manager then filters the report based on say the location he wants and then presses F9 to generate a random number for each line. The next step he does is then sort by random number.

    There is a cell "H4" that is calculated by the HR manager indicating that he wants to select say 25% of the employess now showing in the filtered table. For example if there are 200 employees now shown after being filtered and the HR manager keys in 10% then cell H4 would calculate 20.

    My macro then needs to start at the first filtered line and count down 20 lines and copy and paste those first 20 visible lines to a new sheet. My problem is that all of my attempts are copying the first 20 lines including lines that have been hidden by the filter.

    Any help with either of my attempts is really appreciated. As you probably guessed I am a complete novice........

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Select and copy n number of rows from Filtered Table

    how bout a loop to count h4 occurrence to get your array setup
    then when you select, only select visible cells in the array ?

  10. #10
    Registered User
    Join Date
    10-20-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Select and copy n number of rows from Filtered Table

    Would you mind providing a sample for me?

  11. #11
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Select and copy n number of rows from Filtered Table

    Please Login or Register  to view this content.
    replace "Middle" with location code - case specific

    ps the code looks at column 3..i guessed column 3 was where you had the location code
    change as necessary
    Last edited by humdingaling; 10-24-2013 at 09:31 PM. Reason: updated code to sample sheet

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Select and copy n number of rows from Filtered Table

    attached is sample sheet i used to make the code

    H4 = number of rows
    H5 = filter

    based on region

    ps usually better if you give sample data than people making their own
    just so happened i had some data lying around i could use =)
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro command to select filtered rows only
    By Gandalf21 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-19-2013, 11:31 AM
  2. [SOLVED] Macro to select visible rows containg data in filtered list
    By knevil in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-15-2013, 01:22 PM
  3. Macro to select a specific column of filtered data in a table
    By mgrothendick in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2012, 06:01 PM
  4. Replies: 4
    Last Post: 08-16-2012, 06:20 PM
  5. Using VBA to autofilter and paste over results - can't select only filtered rows.
    By Cornwell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2010, 11:33 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1