Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting rows with the same criteria | Excel Worksheet Functions | |||
Selecting Rows that meet criteria | Excel Worksheet Functions | |||
Selecting rows based on criteria | Excel Discussion (Misc queries) | |||
VBA - Selecting Based on Criteria | Excel Programming | |||
Selecting Sheets based on Cell Criteria. | Excel Programming |