Solved

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

Posted on 2016-08-09
6
25 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 31

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 33

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 31

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

738 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