Solved

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

Posted on 2016-08-09
6
22 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 29

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
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 

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 29

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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