Solved

Excel VBA how can I remove rows where a cell does not meet specific criteria

Posted on 2012-04-02
10
279 Views
Last Modified: 2012-04-05
I have a very large macro which produces an output as shown in DashSample(attached)
I have set the variable CurMoInt to be the number of the report month so the value will be dynamically adjusted monthly.
My goal is to show only the rows that are -.2 times the month or Less and delete rows where the value in Col I is higher than the month times -.2 (negative point 2).
So as an example in February any row with a value .399 or more with be deleted.
I tried the code below without success.
I could really use some help! Thanks

sample view of report
'Remove rows from Dashboard (FY) that don't meet criteria
 HRiskVal = CurMoInt * -0.2
 With Sheets("Dashboard (FY)").Range("I10:I205").Select
     For Each Cell In Selection
        If Cell.Value < HRiskVal Then
               Selection.EntireRow.Select
               Selection.Delete
        End If
    Next Cell
 End With
0
Comment
Question by:Mswetsky
  • 6
  • 2
  • 2
10 Comments
 
LVL 6

Expert Comment

by:wshark83
ID: 37796170
try this


Sub InsertLine()
    Dim i As Long
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Cells(i - 1, 1) <> Cells(i, 1) Then   --> change this line to set your criteria
            Rows(i).Delete
        End If
    Next
End Sub
0
 
LVL 3

Expert Comment

by:DaFranker
ID: 37796590
Your original code uses a "With" block the wrong way. It also uses .Select and Selection, which is unnecessarily rounadabout and much slower than using direct references. Reworking it, it would give something more like this (untested):

'Remove rows from Dashboard (FY) that don't meet criteria
hriskval = curmoint * -0.2
For Each Cell In Sheets("Dashboard (FY)").Range("I10:I205")
    With Cell
        If .Value < hriskval Then
            .EntireRow.Delete
        End If
    End With
Next Cell

Open in new window


However, here's an alternative that in my tests (on my machine - results may vary) proved to be faster and cleaner than other functions of this type (that I tried):
hriskval = curmoint * -0.2
Dim i As Long, r As Range
Set r = Sheets("Dashboard (FY)").Range("I10:I205")
For i = 1 To r.count
    If r(i).Value < hriskval Then r(i).EntireRow.Delete
Next

Open in new window

0
 
LVL 1

Author Comment

by:Mswetsky
ID: 37797173
Sorry for the delayed response.
I tried

HRiskVal = CurMoInt * -0.2
Range("I12").Select
     Dim i As Long
    For i = Cells(205, 1).End(xlUp).Row To 12 Step -1
        If Cells(i - 1, 1).Value < HRiskVal Then
            Rows(i).Delete
        End If
    Next

And no rows were deleted.

I know there will only be 202 rows on the sheet so then I changed the count row to 205.
I also noticed that when I hovered of the Cells(i-1,1) it showed what looked like a row number so I tried adding value but that didn't help either.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 37797238
I tried your second suggestion and it deleted rows but it deleted the wrong ones.
For example it deleted a row that had (3.86)

The values I want are the negative ones that are more negative than (CurMoInt * -0.2)
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 37797247
This is what I tried

hriskval = curmoint * -0.2
For Each Cell In Sheets("Dashboard (FY)").Range("I12:I205")
    With Cell
        If .Value > hriskval Then
            .EntireRow.Delete
        End If
    End With
Next Cell
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.

 
LVL 3

Accepted Solution

by:
DaFranker earned 500 total points
ID: 37797638
Take note of the mathematical comparison operator symbols.

In your last attempt, you put .Value > hriskval, which will mean "if the value is above hriskval, do this:", so it should work and delete the correct rows if you switch around to .Value < hriskval instead.

Also, keep in mind that all the examples so far assume that every single cell in the range (I12 to I205 in your latest code) is formatted as "General" or "Number" and can only contain decimal numbers. If any of them contains text, blanks, dates or other non-pure-number values, you won't get the desired result out of the code put forward so far.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 37797794
Thanks for you quick reply and patience.
I tried both < and > and did not get the results I expected.

The cells are formatted as Number with 2 Dec and there are some 0.00 included.
They were formatted as negative red with parenths so I tried plain -3.79

The range of the current set is -3.79 to 4.15 with approx 25-40 rows that have 0.00s
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 37798087
I figured out why your suggestion didn't work and thank you for the direction.
The values I wanted to compare were in Col I  so when I changed the col ref it worked.
Thank you again for your assistance.

The following works:
    HRiskVal = CurMoInt * -0.2
    Range("I12:I202").Select
    Dim i As Long
    For i = Cells(202, 9).End(xlUp).Row To 12 Step -1
        If Cells(i - 1, 9) > HRiskVal Then
            Rows(i).Delete
        End If
    Next
0
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 37798090
Thanks for your patience
0
 
LVL 6

Expert Comment

by:wshark83
ID: 37806847
seeing as you have used my solution with a few modificiation i think you should split the points accordingly
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

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

12 Experts available now in Live!

Get 1:1 Help Now