looking for Excel function that...

[rgordon83]

deletes entire ROWS that contain a certain keyword anywhere in the row(not just cell contents)

anyone know if this function exists and what it's called?

 

[Skittled]

Sounds more database-y than excel-esque - could you import it into access/other database?

Long story short, not many functions can edit cells other than themselves, so deleting a row doesn't sound like a function thing. I know nothing about macros, but you/someone might, and I assume they could do the job.

Another alternative; export as CSV, write a quick program in your language of choice to do the dirty work, open up again in excel.

 

[Enigmabomb]

I second the CSV. One line of PHP along the lines of:

Your CSV:

Column1,Column2,Column3

PHP:
preg_replace('/\b,\b,YourValue.*?$/',"",file_get_contents($file));


In english: Search for a word, a comma, YourValue optionally followed by other things, as many of them as you'd like, until the end of the line.

This is untested, but should be close.

 

[Enigmabomb]

Im feeling generous.

Here is your code, tested.

<?php

$g = "23,245,34\n35,23,12\n45,45,45";
$g = preg_replace("/\d*,\d*,45$/","",$g);

$g = "Joe,Jim,Rob\nJosh,Jon,Tom\n";
$g = preg_replace("/\w.*,\w.*,Rob/","",$g);

echo $g;

?>

The one caveat is it leaves blank lines where the old ones were, but that wont show up when you re-import to excel.

 

[Enigmabomb]

Wait. Anywhere in the row?! Stand by...

 

[Enigmabomb]

This is dirty, but it works.

$g = preg_replace("/(.*?)Rob(.*?)?/","",$g);

 

[Enigmabomb]

Ok, that actually breaks if Rob starts the line. This works, but again, dirty.

$g = preg_replace("/^(.*?),?Rob,?(.*?)\n/","",$g);

Which in english reads:

If you have a start of the line, Followed by anything, then an optional comma (So in case we're first column) Then our search term Rob Followed by an optional comma (In case were last row) followed by anything until the line break, replace it with nothing.

Sorry about all the posts, but I cant edit after 10 mins. Please let me know how this works for you, I've spent some time on it, and the payoff will be hearing your results.

 

[abhorrent]

This works.

Sub Find_[COLOR=Yellow]YOUKEYWORD[/COLOR]()
    Dim rng As Range
    Dim what As String
    what = "[COLOR=Yellow]YOUKEYWORD[/COLOR]"
    Do
        Set rng = ActiveSheet.UsedRange.Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            Rows(rng.Row).Delete
        End If
    Loop
End Sub

 

[ImagesAndWords]

I've actually looked for something like this in Excel too. Sometimes filtering or removing certain rows in Excel can be a biotch.
Thanks!

 

[rgordon83]

thanks Enigma and Abhorent +reps

 

[LotsOfZeros]

This works.

Sub Find_[COLOR=Yellow]YOUKEYWORD[/COLOR]()
    Dim rng As Range
    Dim what As String
    what = "[COLOR=Yellow]YOUKEYWORD[/COLOR]"
    Do
        Set rng = ActiveSheet.UsedRange.Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            Rows(rng.Row).Delete
        End If
    Loop
End Sub

nice move