THE BORE

General => The Superdeep Borehole => Topic started by: GilloD on September 24, 2008, 05:15:15 PM

Title: N00b Excel Q
Post by: GilloD on September 24, 2008, 05:15:15 PM

I've got a mailing list of 25k names. In every row there are 6 pieces of data- Customer #, First, Last, E-mail, Format Pref and Status. For every ROW whose STATUS is "Unsubscribed", I need to DELETE that row.

How do I do this? I've been able to Find+Replace a CELL, but not a ROW.

Title: Re: N00b Excel Q
Post by: ananus on September 24, 2008, 05:33:04 PM

ctrl + f > replace tab > put the text in it and then in the replace text field leave it blank. click to replace all.

edit: if the shortcut isn't ctrl + f use edit > find

Title: Re: N00b Excel Q
Post by: GilloD on September 24, 2008, 05:34:44 PM

Quote from: ananus on September 24, 2008, 05:33:04 PM

ctrl + f > replace tab > put the text in it and then in the replace text field leave it blank. click to replace all.

Yeah, but this just replaces the individual CELLS, I need to wipe the whole ROW that cell is in

Title: Re: N00b Excel Q
Post by: ananus on September 24, 2008, 05:37:50 PM

ah, i dunno then.

Title: Re: N00b Excel Q
Post by: ananus on September 24, 2008, 06:12:39 PM

tools > macro > macros... > create a new macro. paste this:

Code: [Select]

    rowx = 1
    Do Until Cells(rowx + 1, COLUMN.Value = ""
        If UCase(Right(Cells(rowx, COLUMN).Value, 12)) = "UNSUBSCRIBED" Then
            Cells(rowx, COLUMN).EntireRow.Delete
        Else
            rowx = rowx + 1
        End If
    Loop

where it says COLUMN you have to put the column where unsubscribed is, starting from 1. then execute the macro.

Title: Re: N00b Excel Q
Post by: Bocsius on September 24, 2008, 06:15:28 PM

Try a macro.

Code: [Select]

Sub TryThis()

    Range("A1").Select
    Dim bCont As Boolean
    bCont = True
   
    Dim iRow As Integer
       
    Do
   
        iRow = 1
   
        If (LCase(ActiveCell.Offset(0, 5).Range("A1").Value) = "unsubscribed") Then
            Rows(ActiveCell.Row).Delete
            iRow = 0
        End If
       
        ActiveCell.Offset(iRow, 0).Range("A1").Select
       
        If ActiveCell.Value = "" Then
            bCont = False
        End If
   
    Loop Until Not bCont

End Sub

Edit: late, as per the usual.

Title: Re: N00b Excel Q
Post by: pilonv1 on September 24, 2008, 08:38:14 PM

couldnt you just auto filter for unsubscribed and delete whatever shows up?

Title: Re: N00b Excel Q
Post by: Reb on September 25, 2008, 01:58:05 AM

I rule autofilter as the winning idea.

Title: Re: N00b Excel Q
Post by: Reb on September 25, 2008, 02:00:36 AM

Also, you can sort by that column, so you have all the Unsucribed rows toghether for the deleting.

Title: Re: N00b Excel Q
Post by: pilonv1 on September 25, 2008, 04:18:04 AM

exactly. no need for macro for simple deletion

smh

Title: Re: N00b Excel Q
Post by: Bildi on September 25, 2008, 04:28:50 AM

Quote from: Rebiak on September 25, 2008, 02:00:36 AM

Also, you can sort by that column, so you have all the Unsucribed rows toghether for the deleting.

Yeah, that's what I was going to suggest.  If you'll have trouble sorting after you've deleted some rows, add a column with numbers before you do anything, then sort by that column after deleting the unwanted rows.

Title: Re: N00b Excel Q
Post by: Reb on September 25, 2008, 07:04:32 AM

Stupid IT homos.

Title: Re: N00b Excel Q
Post by: Bocsius on September 25, 2008, 09:12:08 AM

You guys are simply fixing the problem. We were killing it with fire. :(

Title: Re: N00b Excel Q
Post by: CajoleJuice on September 25, 2008, 10:24:19 AM

smh noobs

working with excel all day :-\