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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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,899
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,942

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,899

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,942
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,998
Messages
5,834,808
Members
430,322
Latest member
excelnoobnoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top