Is there a simple way to delete a row in excel by isolating a certain part of that row and deleting it only?

[zotah]

Is there a way to delete a row completely out of excel without manually doing it row by row one at a time.

Lets say I wanted to delete some of the excel rows in a address list that had apartments and Units.

and it looked like this

[TABLE="width: 450"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]1 W 11th Pl[/TD]
[TD](719) 362-9697 [/TD]
[TD]George Young[/TD]
[/TR]
[TR]
[TD]10 Farther Pt[/TD]
[TD](719) 478-8866 [/TD]
[TD]Jones E Peterson[/TD]
[/TR]
[TR]
[TD]10 Gens Ct[/TD]
[TD](719) 942-9570 [/TD]
[TD]Jack F Baker[/TD]
[/TR]
[TR]
[TD]10 Lacewood Ln[/TD]
[TD](719) 723-7839 [/TD]
[TD]Bob B Adams[/TD]
[/TR]
[TR]
[TD]10 Oak Ct[/TD]
[TD](719) 568-6258 [/TD]
[TD]Larry Thomas[/TD]
[/TR]
[TR]
[TD]10 Oak Ct Apt 3207[/TD]
[TD](719) 847-0258 [/TD]
[TD]Peter King[/TD]
[/TR]
[TR]
[TD]10 Oak Ct, Apt 4105[/TD]
[TD](719) 522-9394 [/TD]
[TD]Gary Harris[/TD]
[/TR]
[TR]
[TD]10 Oak Ct, Apt 5106[/TD]
[TD](719) 519-5248 [/TD]
[TD]Steve Jackson[/TD]
[/TR]
[TR]
[TD]10 S Briar Hollow Ln, Unit 51[/TD]
[TD](719) 632-6250 [/TD]
[TD]Rick Phillips[/TD]
[/TR]
[TR]
[TD]10 Sandalwood Dr[/TD]
[TD](719) 784-1050 [/TD]
[TD]Stan M Adams[/TD]
[/TR]
</tbody>[/TABLE]


Let's say I just wanted to delete the rows that just had the word Apt listed in that Row.

I didn't just want to delete Apt but I wanted the whole address to be deleted out of the list and lets say I had over one thousand addresses like this all going down multiple cells in one several columns.

Is there a quick way to isolate all the address that have the word Apt and Unit and delete the intire row of that particular listing and then leave all the rest of the addresses intact?

Thanks for your help and advice,

Zotah

 

[Joe4]

Here is a macro that should do what you want. You simply replace the value you are looking for and the column you want to search at the top of the code (the block in between asterisks).

Sub MyDelete()


    Dim WhatToSearchFor As String
    Dim ColumnToSearch As String
    
' **************************************
'   Enter what to search for
    WhatToSearchFor = "Apt"
'   Enter column to search
    ColumnToSearch = "A"
' **************************************


    On Error GoTo err_fix


'   Keep searching/looping until you reach error 91
    Do
        Columns(ColumnToSearch).Find(What:=WhatToSearchFor, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
'       Delete whole row once found
        ActiveCell.EntireRow.Delete
    Loop
        
err_fix:
'   Message box to return once deletions are complete or other errors are incurred
    If Err.Number = 91 Then
        MsgBox "No more entries of " & WhatToSearchFor & " found in column " & ColumnToSearch, _
            vbOKOnly, "MACRO COMPLETE!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
        
End Sub

 

[zotah]

Here is a macro that should do what you want. You simply replace the value you are looking for and the column you want to search at the top of the code (the block in between asterisks).

Sub MyDelete()


    Dim WhatToSearchFor As String
    Dim ColumnToSearch As String
    
' **************************************
'   Enter what to search for
    WhatToSearchFor = "Apt"
'   Enter column to search
    ColumnToSearch = "A"
' **************************************


    On Error GoTo err_fix


'   Keep searching/looping until you reach error 91
    Do
        Columns(ColumnToSearch).Find(What:=WhatToSearchFor, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
'       Delete whole row once found
        ActiveCell.EntireRow.Delete
    Loop
        
err_fix:
'   Message box to return once deletions are complete or other errors are incurred
    If Err.Number = 91 Then
        MsgBox "No more entries of " & WhatToSearchFor & " found in column " & ColumnToSearch, _
            vbOKOnly, "MACRO COMPLETE!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
        
End Sub

I'm not trained to this point in macros,

Is there away to take me step by step through where I put this macro and where I go in Excel 2010 so I can get the job done with what I want to do. After I do this a few times I'll sure understand alot more about macro's

I touched on macro's a few years back in school but can't remember much at all about them now.

Do I paste this program macro some where in Excel 2010?

Could you take me through a step by step process of what I'm supposed to do with this particular macro you've given me here?

Will I be able to clear out multiple words out of my list, (not just apt. but other words like unit and apt without a period after the t in apt etc.)

There might be a # used as the pound sign for apartment Can I isolate that little letter or digit and delete that entire row as well (Just from the digit #)

How would I change the macro each time I want to go back and delete other rows with other different digits or words that I don't want?

Thanks for answering my questions and helping me out,

Zotah

 

[Daveydoodles]

If you want to avoid using macros I would do the following;

Filter on the column containing the addresses

Filter for the values you want to delete

Clear these cells

Clear the filter

Select the whole column

Press F5

Click special

Select blanks > OK

Right click

Delete... > Entire row

 

[zotah]

If you want to avoid using macros I would do the following;

Filter on the column containing the addresses

Filter for the values you want to delete

Clear these cells

Clear the filter

Select the whole column

Press F5

Click special

Select blanks > OK

Right click

Delete... > Entire row

This works great I'm still having a little problem I was wonder if you could help me on

When I filter out the 3 letters lets say (Apt) (Trlr) (Unit) etc.

I need to filter these out so there isolated away from any other letters or full street names.

For example when I filter out the word (Unit). I also get all the words listed as United

When I filter out the word (Ste) which is how its listed thousands of times for suite

The filter for those 3 letters (Ste) is being grabbed out of every single street that has Ste inside of that word for example it's picking out words like Chesterfield and Homestead (because ste is inside those street names).

Is there anyway that you know of that filters out only (Ste) with blank spaces in front of the letters (Ste) and blank spaces after (Ste) so the blank spaces before (Ste) and the blank spaces after (Ste) can be noticed and seperated from full words that have (Ste) listed in them already like those words I've listed (chesterfied and homested)

I would like to filter out just certain letters only so if there are letters in front of my search or letters directly after my search it ignores those words from being filtered?

Thanks

Zotah

 

[Joe4]

Regarding VBA code, here are a few links that can help get you started:
Getting Started with VBA in Excel 2010
http://www.contextures.com/xlvba01.html

Google/YouTube searches will yield a lot more results and tutorials.

The easiest way to find where you want to place your VBA code is to record a new macro, stop recording, then edit that macro. That will bring up exactly where you need to be in the VB Editor to copy and paste your code.

 

[zotah]

Thanks with everyones help I figured it out without using Macros.

What I did was I went to Sort and Filter and clicked on Filter (2010 Excel Version)

Then I highlighted the column I wanted to Filter and got a down arrow to click on in the first cell

Then I clicked on Text Filter and went to the dropdown that said Contains.

I then put in the specific letter comination I wanted to delete and clicked ok

It worked perfect

Thanks So much for everyones help on this

Zotah