Solved

How do I delete rows in MS Excel VBA if a condition is met

Posted on 2016-08-09
6
21 Views
Last Modified: 2016-09-01
I know how to delete a row in Excel with VBA, but where I am struggling is how to delete rows if a certain condition is met.  For example for any row where Column H (Status) is = to 'Closed' then I wish to delete these rows.  Suggestions ?
0
Comment
Question by:upobDaPlaya
  • 3
  • 2
6 Comments
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points (awarded by participants)
ID: 41749816
You may try this....

Sub DeleteRows()
Dim lr As Long, i As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "H").End(xlUp).Row
For i = lr To 2 Step -1
    If Cells(i, "H") = "Closed" Then
        Rows(i).Delete
    End If
Next i
Application.ScreenUpdating = True
End Sub

Open in new window

1
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41750001
Apply a filter to the data and Column H filter so only "Closed" is visible.

Select the visible rows or even just one column of visible rows and delete. The rows hidden by the filter will be unaffected.

Thanks
Rob H
0
 

Author Comment

by:upobDaPlaya
ID: 41755180
It works great.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:upobDaPlaya
ID: 41755181
Thanks for the assist it works great...
0
 

Author Comment

by:upobDaPlaya
ID: 41755182
Thx for the assist
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41779497
The chosen answer resolves the issue considering the original requirement was to delete the rows based on a condition through VBA not with manual steps.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now