delteting rows via macro

**mike02** · 07-31-2012, 02:59 PM

I was just looking for some help with my macro,

 
Range("V16").Select
    Range(Selection, Selection.End(xlDown)).Select
    Do Until ActiveRange.Value = ""
        If ActiveRange.Value <> "1" Then
            ActiveRange.EntireRow.Delete
        Else
            ActiveRange.Offset(1, 0).Select
        End If
    Loop
End Sub

I have a parameter set up basically if the data doesnt meet specifications then i want it to delete the entire row. the values found start at V16 and proceed down to V324. but V324 could change at anytime.

also, for some reason my if function wont work. I ve tried re writing it, manipulating the sings etc. but to no avail. Here it is:

=If(0.7<=A17<=3,1,"")

thanks for the help!

**day92** · 07-31-2012, 03:11 PM

Mike take a look at this thread and see if it helps..

http://www.excelforum.com/excel-gene...-column-a.html

**mike02** · 07-31-2012, 03:16 PM

thats where i got the code from lol, I just started playing around in vba in excel, so im still learning

**Pauleyb** · 07-31-2012, 03:20 PM

I'm assuming you used day92's code from the thread he points to since they look so similar. In case you missed it, the Selection.End(xlDown) is likely screwing you up.

Looks like you are combining two questions into one thread. In the future please ask as separate questions since it will help people find solutions to similar problems. As for your problem, you are thinking too much like a mathematician. Excel can't calculate a range of values in that way. You have to use =IF(AND(A17<=3, A17>=0.7),1,"").

Pauley

**day92** · 07-31-2012, 03:24 PM

Try this..
=IF(AND(A17>=0.7,A17<=3),1,"")

If you are still having issue please upload some sample data with expected results.

**mike02** · 07-31-2012, 03:32 PM

thanks pauly,

I eraased that line and it works well, Only thing is It isnt running after the: Do until ActiveRange.Value= ""

Do i put anything in the quotes? and the rows that do = one in the Range i want to fill in blue. how would you do that?

Would it be a Formating cmd?

**Kelshaer** · 07-31-2012, 03:42 PM

Would you like to try this code

Sub DeleteRows()
Dim i As Long
With ActiveSheet
    For i = .Range("V" & .Rows.Count).End(xlUp).Row To 2 Step -1
        If .Range("V" & i).Value <> "1" Then .Rows(i).Delete
    Next
End With
End Sub

**mike02** · 07-31-2012, 03:45 PM

Work Like a charm, Is there a way to leave the first 3 rows? thanks ALOT!

**Kelshaer** · 07-31-2012, 03:46 PM

Sub DeleteRows()
Dim i As Long
With ActiveSheet
    For i = .Range("V" & .Rows.Count).End(xlUp).Row To 4 Step -1
        If .Range("V" & i).Value <> "1" Then .Rows(i).Delete
    Next
End With
End Sub