Selecting rows based simultaneously on 3 criteria

[miserere]

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]

A very very friendly bump

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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]

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

[Dave Peterson]

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