deleting rows based on text in a cell

[bill151515]

is it possible to have a macro select all rows in a worksheet that have the text "delete row" in the cells of column s??? and then delete all those rows

could be rows 1,21,22,55,88 one day and then rows 5,11,99 another

 

[SteveO59L]

What is the criteria for the row deletion ?

 

[bill151515]

you would look down the cells in column s anny cell that has text "delete row" would be selected for entire row deletion

 

[SteveO59L]

Range("s65536").End(xlUp).Select

While ActiveCell.Row > 1

If ActiveCell.Value = "delete row" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Wend

 

[btadams]

Here's a macro you can assign to a custom macro button on your toolbar. Select the cell that contains the text you want deleted and then run this macro:

Option Explicit

Sub DeleteIfSame()
Dim x As String

'   Abort if a range isn't selected
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a single cell", vbOKOnly + vbInformation, "Invalid Range Selection"
        Exit Sub
    End If
    
    If Selection.Cells.count > 1 Then
        MsgBox "Please select a single cell", vbOKOnly + vbInformation, "Invalid Range Selection"""
        Exit Sub
    End If
    
    On Error GoTo endmacro
    x = ActiveCell.Value
    If x = "Error 2042" Then x = ActiveCell.Formula
    
   
    If x = Empty Then
        intResponse = MsgBox("The current cell is empty. Do you wish to delete all rows with an empty cell in the current column?", vbOKCancel, "Delete Rows If Same")
        If intResponse = vbCancel Then
            Exit Sub
        End If
    End If
    intResponse = MsgBox("This macro will delete all rows with " & x & " in the current column", vbOKCancel, "Delete Rows If Same")
    If intResponse = vbOK Then
        With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
'            ActiveCell.EntireColumn.Select
            With ActiveCell.EntireColumn
                .AutoFilter Field:=1, Criteria1:=x
                Rows("2:65536").SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .AutoFilter
            End With
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With
    End If
    
endmacro:
    Application.Calculation = xlCalculationAutomatic
 End Sub

 

[bill151515]

keep getting and error


c = error 2042???

 

[SteveO59L]

With which code ?

 

[Superstar31]

Range("s65536").End(xlUp).Select

While ActiveCell.Row > 1

If ActiveCell.Value = "delete row" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Wend

Using the same macro, instead of delete row, I want it to delete a row(s) if they dont have a number in them (the number is random), but the cell will either have a number or have a -- or be blank or have letters so my only criteria is that there be a number in the row otherwise delete it.

 

[btadams]

try:

Range("a65536").End(xlUp).Select

While ActiveCell.Row > 1

If Not IsNumeric(ActiveCell.Value) Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Wend

 

[Superstar31]

Whoops almost forgot, how do I add that to my current macro?

Current Macro

Sub CleanUP_Friday_Reports()
'
' CleanUP_Friday_Reports Macro
' Marc Was Here
'
' Keyboard Shortcut: Ctrl+a
'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(12, 1), Array(29, 1), Array(43, 1), Array(53, 1), _
Array(57, 1), Array(64, 1), Array(72, 1), Array(85, 1), Array(92, 1), Array(100, 1), Array( _
108, 1), Array(121, 1)), TrailingMinusNumbers:=True
Rows("1:14").Select
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireColumn.AutoFit
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub


OK so what I did, was paste my 2nd macro before end sub, so i pressed enter at DataOption1:=xlSortNormal

and clicked paste

Range("a65536").End(xlUp).Select

While ActiveCell.Row > 1

If Not IsNumeric(ActiveCell.Value) Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Wend

but **** its a slow macro

and i'll be **** it deleted everything (not exactly what i wanted)

i take it back it worked BUT, and its a big but, some of the cells further down on my list are being typecasted as numbers even though they are blank is their a way to fix that also?

maybe I can edit the macro to say is the number is column 1 above 1? yes? leave alone? no, delete row

but how?