vba to delete rows based on certain conditions in Column A

D

DeFacto

New Member
Joined
Jul 29, 2004
Messages
33
Hi!

I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):

Sub DeleteRowOnCondition()
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub

As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).

Any help you can lend will be much appreciated!
 

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.
Sort by date Sort by votes
try something like this

Code: 
Sub DeleteRowsFromBottom()
Dim i As Long, LastRow As Long

Application.ScreenUpdating = False

'finds last used row in column A
LastRow = Range("A65536").End(xlUp).Row

'begin loop to check each row
For i = LastRow To 1 Step -1
    'if cell contains defacto delete
    If mid(Cells(i, "A"),8,7) = "Defacto" Then Rows(i).Delete
Next i

Application.ScreenUpdating = True

End Sub
 
Upvote 0
That's great... works like a charm... thanks a bunch!

One more quick question: I used the "<>" in place of the "=" in my code on the next worksheet so that the macro deleted all the rows that don't meet that condition. This, too, works except that it also deletes my header row. Anyway to easily get around this?
 
Upvote 0
this line

Code: 
For i = LastRow To 1 Step -1

change the 1 to whatever the first row of your data is....

eg if your header is in row 1 and your data starts in row 3 it would look like

Code: 
For i = LastRow To 3 Step -1
 
Upvote 0
TheNoocH said:
try something like this

Code: 
Sub DeleteRowsFromBottom()
Dim i As Long, LastRow As Long

Application.ScreenUpdating = False

'finds last used row in column A
LastRow = Range("A65536").End(xlUp).Row

'begin loop to check each row
For i = LastRow To 1 Step -1
    'if cell contains defacto delete
    If mid(Cells(i, "A"),8,7) = "Defacto" Then Rows(i).Delete
Next i

Application.ScreenUpdating = True

End Sub
Click to expand...


I have the same kind of problem but what I need is for the rows to be deleted if "Alaska" is not in column "C".

I have already sorted the data so that Alaska is at the top but now need to get rid of the rest of the data as it is unecessary.

WG
 
Upvote 0
rows("1:1").Autofilter

rows("1:1").Autofilter Field:=3, Criteria1:="<>*Alaska*"

maxrow=range("A" & rows.count).end(xlup).row
if maxrow>1 then
rows("2:" & maxrow).delete
endif

rows("1:1").autofilter

In Above code
1 is Header Row
3 is Column containing Alaska
2 is starting of your data
 
Last edited:
Upvote 0
yadavrahul143 said:
rows("1:1").Autofilter

rows("1:1").Autofilter Field:=3, Criteria1:="<>*Alaska*"

maxrow=range("A" & rows.count).end(xlup).row
if maxrow>1 then
rows("2:" & maxrow).delete
endif

rows("1:1").autofilter

In Above code
1 is Header Row
3 is Column containing Alaska
2 is starting of your data
Click to expand...


thanks yadavrahul143 I adjusted for my rows and dropped the final autofilter and it works like a charm.
 
Upvote 0
TheNoocH said:
try something like this

Code: 
Sub DeleteRowsFromBottom()
Dim i As Long, LastRow As Long

Application.ScreenUpdating = False

'finds last used row in column A
LastRow = Range("A65536").End(xlUp).Row

'begin loop to check each row
For i = LastRow To 1 Step -1
    'if cell contains defacto delete
    If mid(Cells(i, "A"),8,7) = "Defacto" Then Rows(i).Delete
Next i

Application.ScreenUpdating = True

End Sub
Click to expand...


Just curious and trying to learn. Why doesn't this code require an end if statement to work?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Removing Left Text from a Cell with condition VBA
Replies
3
Views
240
Peter_SSs
Peter_SSs
E
  • Solved
Delete row based on whether cell in alternate column in row has data
Replies
3
Views
153
LazyBug
LazyBug
M
  • Question
VBA - Deleting Entire Row Based on a Condition
Replies
2
Views
615
jolivanes
jolivanes
Z
  • Question
how to delete columns in a pattern.
Replies
5
Views
230
Zeema
Z
I
  • Solved
Select next row at column A after row has been deleted
Replies
4
Views
141
ipbr21054
I

Forum statistics

Threads
1,216,342
Messages
6,130,114
Members
449,558
Latest member
andyamcconnell

Share this page

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
Back
Top