Selecting rows based simultaneously on 3 criteria
 

hi,
Is it possible to make a macro selecting/deleting rows of data based on
3 conditions placed in 3 columns (e.g. Col1=0 And Col2=0 And Col3<"").
I searched through the web for it but didn't find anything appropriate
and working.

I want to delete those rows but actually selection would do just fine.
I can't use autofiltering becouse of layout.

Thanks for any hint or code that would do the above.

Best Regards,
miserere :(


--
miserere
------------------------------------------------------------------------
miserere's Profile: http://www.excelforum.com/member.php...o&userid=27440
View this thread: http://www.excelforum.com/showthread...hreadid=469510

Selecting rows based simultaneously on 3 criteria
 

A very very friendly bump :)


--
miserere
------------------------------------------------------------------------
miserere's Profile: http://www.excelforum.com/member.php...o&userid=27440
View this thread: http://www.excelforum.com/showthread...hreadid=469510

Selecting rows based simultaneously on 3 criteria
 

miserere,

The macro below will delete the rows with your criteria.

HTH,
Bernie
MS Excel MVP


Sub DeleteRowsForMiserere()
Dim myRows As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

Range("A1").EntireColumn.Insert
Range("A1").Value = "Keep"
Range("A2").FormulaR1C1 = _
"=IF(AND(RC[1]=0,RC[2]=0,RC[3]<""""), " & _
"""Trash"",""Keep"")"
myRows = ActiveSheet.UsedRange.Rows.Count
Range("A2").Copy Range("A2:A" & myRows)
Application.Calculate
With Range(Range("A2"), Range("A2").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A2")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub




"miserere" wrote in message
...

hi,
Is it possible to make a macro selecting/deleting rows of data based on
3 conditions placed in 3 columns (e.g. Col1=0 And Col2=0 And Col3<"").
I searched through the web for it but didn't find anything appropriate
and working.

I want to delete those rows but actually selection would do just fine.
I can't use autofiltering becouse of layout.

Thanks for any hint or code that would do the above.

Best Regards,
miserere :(


--
miserere
------------------------------------------------------------------------
miserere's Profile: http://www.excelforum.com/member.php...o&userid=27440
View this thread: http://www.excelforum.com/showthread...hreadid=469510

Selecting rows based simultaneously on 3 criteria
 

I don't understand why you couldn't use data|filter|autofilter.

It looks like it work ok to me.

But this may get you started:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim delRng As Range

Set wks = Worksheets("sheet1")
With wks
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = 0 _
And .Cells(iRow, "C").Value = 0 _
And .Cells(iRow, "K").Value = "" Then
If delRng Is Nothing Then
Set delRng = .Cells(iRow, "A")
Else
Set delRng = Union(.Cells(iRow, "A"), delRng)
End If
End If
Next iRow
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Select '.delete '??
End If

End Sub

Be aware that an empty cell will have a .value of 0. If that's important, you
can add a couple of things to avoid those rows, too.

(I used A,C,K for my columns--change to what you want.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

miserere wrote:

hi,
Is it possible to make a macro selecting/deleting rows of data based on
3 conditions placed in 3 columns (e.g. Col1=0 And Col2=0 And Col3<"").
I searched through the web for it but didn't find anything appropriate
and working.

I want to delete those rows but actually selection would do just fine.
I can't use autofiltering becouse of layout.

Thanks for any hint or code that would do the above.

Best Regards,
miserere :(

--
miserere
------------------------------------------------------------------------
miserere's Profile: http://www.excelforum.com/member.php...o&userid=27440
View this thread: http://www.excelforum.com/showthread...hreadid=469510

--

Dave Peterson

Selecting rows based simultaneously on 3 criteria
 

Oops. I didn't see Bernie's reply.

miserere wrote:

hi,
Is it possible to make a macro selecting/deleting rows of data based on
3 conditions placed in 3 columns (e.g. Col1=0 And Col2=0 And Col3<"").
I searched through the web for it but didn't find anything appropriate
and working.

I want to delete those rows but actually selection would do just fine.
I can't use autofiltering becouse of layout.

Thanks for any hint or code that would do the above.

Best Regards,
miserere :(

--
miserere
------------------------------------------------------------------------
miserere's Profile: http://www.excelforum.com/member.php...o&userid=27440
View this thread: http://www.excelforum.com/showthread...hreadid=469510

--

Dave Peterson