Need Some Help with If / Then Statements

[anees420]

here is what i'm trying to do

I need a macro that does the following


----I have a diagram explaining what i'm trying to do ... this was the best i knew how to get my point across

click on the link to view it

LINK TO DIAGRAM

 

[Jon von der Heyden]

Hi anees420 and welcome to the board.

That shouldn't be too difficult to describe. If I follow your diagram correctly, basically, you want all rows delete where the value in column G is greater than 100,000 or equal to 0.

The following assumes you have field names / column labels in row 1.

[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] DelRows()
    [COLOR=blue]Dim[/COLOR] rngValues [COLOR=blue]As[/COLOR] Range
    [COLOR=blue]Const[/COLOR] lngCriteria1 [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] = 100000
    [COLOR=blue]Const[/COLOR] lngCriteria2 [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] = 0
 
    [COLOR=blue]Set[/COLOR] rngValues = Range(Cells(1, 8), Cells(Rows.Count, 8).End(xlUp))
 
    ActiveSheet.AutoFilterMode = [COLOR=blue]False[/COLOR]
    [COLOR=blue]With[/COLOR] rngValues
        .AutoFilter field:=1, Criteria1:=">" & lngCriteria1, _
            Operator:=xlOr, Criteria2:=lngCriteria2
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Edit: Changed to column H. Revisitng your diagram, you appear to want the crietria applied to column H. You seem to have the same condition, regardless of whether or not column G is between 4000 and 100000.

 

[anees420]

Thank you for the quick reply....

but the macro did not work

---Trust me the Diagram explains it---

HELP HELP

 

[Jon von der Heyden]

You diagram suggests the following:

If value in column G is between 40000 and 100000 Then
If value in column H is 0 Then delete the row.
If value in column H is greater than 100000 Then delete the row.

If value in column G is not between 40000 and 100000 Then
If value in column H is 0 Then delete the row.
If value in column H is greater than 100000 Then delete the row.

Why is it important to test whether or not the value in column G is within 40000 and 100000 if the resulting action is the same in both instances?

but the macro did not work

Care to elaborate?

 

[anees420]

If value in column G is between 40000 and 100000 Then
If value in column H is 0 Then delete the row. <---- KEEP ROW
If value in column H is greater than 100000 Then delete the row.

If value in column G is not between 40000 and 100000 Then
If value in column H is 0 Then delete the row.
If value in column H is greater than 100000 Then delete the row.


Hope that helps... Thank you again

 

[anees420]

G1 = 120,000
H1 = 90,000
Keep Row

G2 = 90,000
H2 = 0
Keep Row

G3 = 80,000
H3 = 95,000
keep Row

----------------------

G4 = 90,000
H4 = 105,000
Delete Row

G5 = 140,000
H5 = 0
Delete Row

G6 = 155,000
H6 = 206,000
Delete Row


---- Maybe you can try these values--- these are random numbers... i hope this helps... thank you

 

[Jon von der Heyden]

I seem to have overlooked the 2nd rule. Try this:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] DeleteRows()
    [COLOR="Blue"]Dim[/COLOR] rngValues [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Dim[/COLOR] rngDelete [COLOR="Blue"]As[/COLOR] Range
    
    [COLOR="Blue"]Set[/COLOR] rngValues = Range(Cells(1, 7), Cells(Rows.Count, 8).End(xlUp))
    
    ActiveSheet.AutoFilterMode = [COLOR="Blue"]False[/COLOR]
    [COLOR="Blue"]With[/COLOR] rngValues
        .AutoFilter field:=1, Criteria1:=">=" & 40000, Operator:=xlAnd, Criteria2:="<=" & 100000
        .AutoFilter field:=2, Criteria1:=">" & 100000
        [COLOR="Blue"]Set[/COLOR] rngDelete = .Offset(1).SpecialCells(xlCellTypeVisible)
        .AutoFilter field:=1, Criteria1:="<" & 40000, Operator:=xlOr, Criteria2:=">" & 100000
        .AutoFilter field:=2, Criteria1:="=" & 0, Operator:=xlOr, Criteria2:=">" & 100000
        [COLOR="Blue"]Set[/COLOR] rngDelete = Union(rngDelete, .Offset(1).SpecialCells(xlCellTypeVisible))
        .AutoFilter
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    rngDelete.EntireRow.Delete
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[anees420]

----- FANTASTIC ---- the macro works GREAT !!!! THANK YOU

if its not to much trouble can it also be sorted out

ascending from column H then G

&

Run the macro in a loop every 30 seconds

also how would i be able to stop it once its going without closing down excel or the workbook

 

[anees420]

I got the loop out of the way

all is left is the sorting


Thank you again

 

[Jon von der Heyden]

Try;

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] DeleteRows()
    [COLOR="Blue"]Dim[/COLOR] rngValues [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Dim[/COLOR] rngDelete [COLOR="Blue"]As[/COLOR] Range
    
    [COLOR="Blue"]Set[/COLOR] rngValues = Range(Cells(1, 7), Cells(Rows.Count, 8).End(xlUp))
    
    ActiveSheet.AutoFilterMode = [COLOR="Blue"]False[/COLOR]
    [COLOR="Blue"]With[/COLOR] rngValues
        .AutoFilter field:=1, Criteria1:=">=" & 40000, Operator:=xlAnd, Criteria2:="<=" & 100000
        .AutoFilter field:=2, Criteria1:=">" & 100000
        [COLOR="Blue"]Set[/COLOR] rngDelete = .Offset(1).SpecialCells(xlCellTypeVisible)
        .AutoFilter field:=1, Criteria1:="<" & 40000, Operator:=xlOr, Criteria2:=">" & 100000
        .AutoFilter field:=2, Criteria1:="=" & 0, Operator:=xlOr, Criteria2:=">" & 100000
        [COLOR="Blue"]Set[/COLOR] rngDelete = Union(rngDelete, .Offset(1).SpecialCells(xlCellTypeVisible))
        .AutoFilter
        .Sort key1:=Range("H1"), key2:=Range("G1"), header:=xlYes
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    rngDelete.EntireRow.Delete
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]