Really Silly Problem
Really Silly Problem
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Really Silly Problem

  1. #1
    Guest

    Default

     
    Hi all,
    In my program generated report, I have

    Agent: XX
    Total: YY
    Monday ---
    Tuedday ---
    ....... Sunday

    Now, we have about 157 agents. My boss is only interested in the Agent name and the total. How do I delete the days and the rows using vba?? tried using the Find & Replace, but cannot get it to delete the rows as well. Thanks

  2. #2
    Guest

    Default

    Please give us more info

  3. #3
    Guest

    Default

    OK..
    We use a software by bluepumpkin, to control call centre agent adherence. At the end of the week, I will export a report generated by the software to Excel format. The report looks something like:

    Agent 1
    Total : xxx
    -- Breakdown --
    Monday
    Tuesday
    ... all the way till Sunday

    Agent 2
    Total : yyy hrs
    -- Breakdown --
    Monday - a hrs
    Tuesday - b hrs
    ... all the way till Sunday

    My boss is only interested in the Total. How do I Delete all the rows that contain (Monday, Tuesday, Wednesday..... Sunday)?? I tried using the Replace command, but it won't delete entire rows. Help, guys!! Thanks





  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, here's one way to attack it. Seeing as every day of the week has a name that ends in the same 3 letters "d-a-y", and none of your wanted text ends in "d-a-y", how about hunting down those cells in column A (assuming that's where the days are listed from your import) that end in "d-a-y", and deleting them. Try this macro to see if we are on the right track. First though, copy your data onto a test worksheet, to be sure you don't lose data if this is not what you want.

    ''''''''''''''''''''''''''''''''''''''''

    Sub DeleteDays()
    Application.ScreenUpdating = False
    Dim theCol As Range, cell As Range, RtoSel As Range
    Dim LtoSel As String
    Set theCol = Range(Range("A1"), Range("A65536").End(xlUp))
    LtoSel = "day"
    For Each cell In theCol
    If Right(cell, 3) = LtoSel Then
    If RtoSel Is Nothing Then
    Set RtoSel = cell
    Else
    Set RtoSel = Application.Union(RtoSel, cell)
    End If
    End If
    Next
    On Error GoTo e
    RtoSel.EntireRow.Delete
    [A1].Select
    Application.ScreenUpdating = True
    Exit Sub
    e:
    MsgBox "There are no days to delete.", 64, "Time for a beer !"
    [A1].Select
    End Sub

    '''''''''''''''''''''''''''''''''''''''

    HTH

  5. #5
    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

      
    This can be easily accomplished with a Custom AutoFilter (see the Data | Filter | AutoFilter... menu command) using the following criteria...

    Show rows where:
    does not begin with | Agent
    And
    does not begin with | Total

    After applying the filter delete all visible rows.

    [ This Message was edited by: Mark W. on 2002-03-05 07:18 ]

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
  •  

 

 
DMCA.com