Removing certain rows based on type macro??
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Removing certain rows based on type macro??

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a file with name, address city state zip. What I would like to do is have a macro that keeps certain states in the list but removes all others and if possible put those bad states on a seperate worksheet??

    Now, I'm not that great at code but if it has to be, I think I need to know how to put the code in. Any help would be appreciated.


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not quite with you, what criteria would you use to determine whether or not a state is bad? (Apart from Utah, of course).

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In my line of work, we can't deal with certain states. So what I want to do is have the ability to keep certain states and remove and place on another worksheet, all others.

    Hope that's clear. Thanks for replying

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can see how it might be done, but it might be pretty tricky to say the least, especially not knowing precisely what you want. I'm on my day off though and at a loose end, so if you'd like to send me a sample worksheet, along with details of what states you want excluding etc. I could get back to you within an hour or two, if you like.



    [ This Message was edited by: Mudface on 2002-03-18 14:34 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, I'll prepare a sample list and email it to you. Thanks!

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Wasn't that difficult, but getting the file back to has been a nightmare (bloody AOL ). In case you don't get it, the E-Mail I sent was as follows: -

    The following works for a couple of your requirements, but finding whether an entry is or is not a state is beyond me. The best I can suggest for that is to type in the names/ abbreviations of all the states, define a name for them, click on the column with your state entries and select Data-Validation, choose List and in the source box, put =States. With the column still selected, click on Tools-auditing-Show Toolbar (if necessary) and click on circle invalid data, then copy, paste and delete the rows by hand. You can leave the validation intact so that future entries can't be anything but a state.

    The modifications I've made to your original sheet were as follows: -

    1. Inserted a new worksheet and typed in the names of the states you want filtered out and named the range of those states MyStates. Also given your data column headings (important).
    2. Inserted a command button on sheet1 and named it Extract. Click on this or press Alt-C to run the macro.
    3. Opened up the VBA editor and inserted a new module and a new procedure called ExtractStates.

    The code transfers filtered data from Sheet1 to Sheet2 based on the criteria in the MyStates range on Sheet4. You'll need to change the code accordingly if you can't fit this in with your current workbook.

    Hope it works alright for you, it would have been with you quicker but Aol sucks big time ,

    Regards

    Chris


    The code in the sub is: -

    Public Sub ExtractStates()

    Application.ScreenUpdating = False
    Sheet1.Rows(1).Copy Destination:=Sheet2.Range("A1")
    Sheet1.UsedRange.AdvancedFilter Action:=xlFilterInPlace, _
    CriteriaRange:=Sheet4.Range("MyStates")
    On Error Resume Next
    Sheet1.UsedRange.CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
    Destination:=Sheet2.Range("A65536").End(xlUp).Offset(1, 0)

    Sheet1.Cells(1, 1).CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0

    With Sheet1.Range("a1")
    .AutoFilter field:=4, Criteria1:="="
    .CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
    Sheet2.Range("A65536").End(xlUp).Offset(1, 0)
    .CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    Sheet1.AutoFilterMode = False
    Sheet2.UsedRange.Columns.AutoFit
    Application.ScreenUpdating = True

    End Sub

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