Removing certain rows based on type macro??

jbyrne

Board Regular
Joined
Feb 22, 2002
Messages
178
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Not quite with you, what criteria would you use to determine whether or not a state is bad? (Apart from Utah, of course:)).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top