N00b Excel Q

[GilloD]

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.

[ananus]

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

[GilloD]

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

[ananus]

ah, i dunno then.

[ananus]

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.

[Bocsius]

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.

[pilonv1]

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

[Reb]

I rule autofilter as the winning idea.

[Reb]

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

[pilonv1]

exactly. no need for macro for simple deletion

smh

[Bildi]

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.

[Reb]

Stupid IT homos.

[Bocsius]

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

[CajoleJuice]

smh noobs

working with excel all day