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

 

[anees420]

DUDE you're a cool guy.... Thank you

 

[Jon von der Heyden]

Actually, you need to run the delete statement before the sort:

[COLOR=#0000ff]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
        rngDelete.EntireRow.Delete
        .Sort key1:=Range("H1"), key2:=Range("G1"), header:=xlYes
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]

[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[anees420]

these is probably easy for you

here is what i'm trying to do

1. move the data from cells E1 into C1 and place a space in between ( data from C1 comes before E1 )

2. place a the word "LINK" into cell E1 (the hyper-link points to http://maps.google.com/(data in the new C1 - but the spaces would be replaced with "+")

here is a example

current C1 = 123 century dr.
current E1 = 90001

new C1 = 123 century dr. 90001
new E1 = "Link" <-- actual work (the work link points to http://maps.google.com/123+century+dr.+90001)

this would be repeated down the worksheet


Thank you..

 

[Jon von der Heyden]

Hi Anees420

Since this is a new question could you please ask it in a new thread?

Thank you.