deleting rows based on text in a cell

bill151515

Board Regular
Joined
May 19, 2005
Messages
87
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

bill151515

Board Regular
Joined
May 19, 2005
Messages
87
you would look down the cells in column s anny cell that has text "delete row" would be selected for entire row deletion
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
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

Well-known Member
Joined
Jan 6, 2003
Messages
1,936

ADVERTISEMENT

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:

Code:
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
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896

ADVERTISEMENT

With which code ?
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
SteveO59L said:
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

Well-known Member
Joined
Jan 6, 2003
Messages
1,936
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

Active Member
Joined
Nov 10, 2005
Messages
496
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,450
Messages
5,572,181
Members
412,447
Latest member
immy
Top