Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: copying data and skipping hidden lines

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Christopher
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I work in a call center, and I do daily reports in Excel on call information for our customer service reps. I have a program called HiPath Composer which generates this data, and my Excel workbook covers each day for a month and does a lot of calculating at the end of the month. Because of my need for totalling at the end, each CSR must remain on the same line on each day. As not everyone works every day, I hide a person's line if they did not work on a particular day and no data was generated for them.


    So here's the problem: This is a huge amount of typing every day and I want to automate the process. Composer will export into an Excel file. However, when I try to copy and paste from the exported file to that day's spreadsheet, I am unable to because some lines in the Excel document are hidden and Excel wants to automatically fill those in. What I need to happen when I copy is for Excel to skip lines that are hidden and only put data into the lines that are showing. I've attempted to set up filters for this and such, but unless I copy line by line (which takes nearly the time of typing the data myself), it will not ignore the hidden lines. Is there an Excel function that will cause the hidden lines to be ignored when copying data or will I need some sort of VB application for this? If necessary, I can send you an example of my Excel workbook and the exported Composer file. HELP!!!

    [ This Message was edited by: mozzer13 on 2002-05-10 12:40 ]

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    124
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I understand you correctly, you just want to copy data without the hidden rows and columns?

    Just select the range of data to copy and then:

    Edit/go to/special/visible cells only/ok

    Now only the visible cells are selected and you can copy and paste.

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Christopher
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually, I want to copy data into a spreadsheet that has hidden lines. I want my copied data to skip the hidden lines and only fill in lines that are visible. I tried using the function you suggested, but it isn't working. Thanks for giving it a try!

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    MOzzer,

    I don't suppose your hidden rows have any uniformity to them do they ?

    I'm guessing they're basically random....

    Any number-sequence ? :

    row 2,3,4,6,7,8,10,11,12 etc hopefully ?

    Chris

  5. #5
    New Member
    Join Date
    May 2002
    Location
    Christopher
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    They are random, just whomever isn't working that day. No data will come up for them on my reports if they didn't log into the phones, so I hide their line on the spreadsheet for that day.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The Paste area must be contiguous.

  7. #7
    New Member
    Join Date
    May 2002
    Location
    Christopher
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Could a Visual Basic app be added to make it skip hidden lines when I paste in copied data?

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-13 10:52, mozzer13 wrote:
    Could a Visual Basic app be added to make it skip hidden lines when I paste in copied data?
    VBA's Paste method would have the same restriction. I suppose someone could write a VBA to do a row-by-row copy and paste, but that would be very inefficient. Perhaps you should re-think your procedures. Why are are you hiding rows?

  9. #9
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In the text you want to import:
    - is the order always the same ? i.e. John's entry first , then Tom then ****
    - If Tom didn't work Row 1 would be john,row 2 ****

  10. #10
    New Member
    Join Date
    May 2002
    Location
    Christopher
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark: I hide rows I don't need that day in order to keep each person on the same row every day. At the end of the month, all of their phone statistics are totalled for me, and if they are in a different place every day, those totals come out wrong.



    Nimrod: It is the same order every day. For example, Nigel is line 1 and Tony is line 2. If Nigel did not work on Monday, he remains line 1, I just hide that line for that day so that I know to put Tony's stats in first that day and to leave Nigel blank. The imported text is always the same order, only some people don't show up on various days because they aren't working.

    [ This Message was edited by: mozzer13 on 2002-05-13 12:11 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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