Delete row if cell contains specific data

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
I need a quick macro that will search column D for the words "balance forward" then delete the entire row. The macro needs to loop through many rows and it contain more than one "balance forward" reference.

Can someone help? I'm just not good at macros yet.

Thanks,
Shirlene
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
try this
Code:
Sub drow()
Dim LR As Long
LR = Cells.SpecialCells(xlCellTypeLastCell).Row
    For i = LR To 2 Step -1
        If Cells(i, 4) <> "Balance Forward" Then
        Rows(i).EntireRow.Delete
        End If
    Next
 End Sub
 

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Thanks, Texasalynn, but that deleted every single row from my table and left only the headings. Egad, what happened?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Shirlene

Could you not just use a filter to hide the rows?

Do they really need deleted?
 

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Yes, my customer really wants them deleted. I already suggested the filter and that is not what he wants. I'm trying to step through the macro right now to see where it is failing but not having a lot of luck.
 

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
This macro deletes the grand total row then every single row from the bottom up, regardless of the text in Column D. It stops when it gets to Row 1 where the headers are.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Well perhaps the problem is that the code is looking for data not equal to "Balance Forward"?
 
L

Legacy 51064

Guest
The Autofilter method works faster than the loop when you have large amounts of data.

Code:
'Filters the and deletes the data
Sheets("Database").Activate

    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="Balance Forward"
        
    ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp

ActiveSheet.AutoFilterMode = False
 

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Yes, I thought that as well and changed to an '=' sign. I reran the macro and absolutely nothing at all happened. I kept thinking "I'll wait until it is done" but it never really started. Again, I tried stepping through but nothing at all.....
 

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
98illini, thanks so much. I didn't think about using the autofilter and then deleting the rows. I guess it was just too simple for my simple brain! It worked like a charm.

Thanks to Norie and Texasalynn, too for your help.

Shirlene
 

Forum statistics

Threads
1,181,418
Messages
5,929,793
Members
436,694
Latest member
dpatete

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