VBA Command to delete the rows.

**mohan_rajun** · 10-25-2010, 02:16 AM

Hello,

I have created the below macro code to delete all the rows containing "account" in column B.

Can you please help me using the below VBA codes, using IF...THEN condition.

Range("B1").Select
Selection.AutoFilter Field:=1, Criteria1:="=*account*", Operator:=xlAnd
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

**JBeaucaire** · 10-25-2010, 02:42 AM

This is how I would write that:

Dim LR as Long

Range("B1").AutoFilter
Range("B1").AutoFilter Field:=1, Criteria1:="=*account*"
LR = Range("B" & Rows.Count).End(xlUp).Row

If LR > 1 Then Range("B2:B" & LR).Delete xlShiftUp
ActiveSheet.AutoFilterMode = False

**MarvinP** · 10-25-2010, 02:47 AM

Try this

Sub DeleteAccountRows()
Dim RowCtr As Double
Dim LastRow As Double

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

For RowCtr = 1 To LastRow
    If InStr(Cells(RowCtr, "B"), "account") > 0 Then
       Rows(RowCtr & ":" & RowCtr).Delete Shift:=xlUp
    End If
Next RowCtr
End Sub

**MarvinP** · 10-25-2010, 02:52 AM

Because you are shifting deleted rows up it might be better to count backwards instead of forwards. Change the For line to:

For RowCtr = LastRow to 1 Step -1

**DonkeyOte** · 10-25-2010, 02:56 AM

I would advise you read through: http://www.xtremevbtalk.com/showpost...70&postcount=4

ie if you intend to use AutoFilter then utilise SpecialCells etc...

edit: full tutorial link: http://www.xtremevbtalk.com/showthread.php?t=300757

**MarvinP** · 10-25-2010, 02:59 AM

I think the Jerry's code will work quicker but may have a problem if there are blank rows.

Excel people always assume users put data in well defined tables or lists.

**MarvinP** · 10-25-2010, 03:03 AM

Thanks for the link. I study now.

**mohan_rajun** · 10-25-2010, 06:44 AM

Hi JBeaucaire,

Thanks for the help... It worked out....

**JBeaucaire** · 10-25-2010, 10:01 AM

Originally Posted by mohan_rajun

Hi JBeaucaire,

Thanks for the help... It worked out....

My pleasure, glad it works for you.

Originally Posted by MarvinP

I think the Jerry's code will work quicker but may have a problem if there are blank rows. Excel people always assume users put data in well defined tables or lists.

I don't assume it, but I am prepared to counsel OPs to correct their data layout into standardized tables/lists when the "blanks in my design" does popup, part of what we have to do, eh?