How to select rows that meet multiple criteria from a column?

Question

I am trying to copy and paste the rows based on column A that appear in column B to a new sheet (e.g. copy and paste all rows that contain 1, 2 and 7 in column A to a new sheet). I know a less smart way using macro. I believe using a nested loop will make life easier (when column B is a long list), however, mine did not work. Please see my LessSmartWay code and FailedSmartWay code below.

The table looks like this:

A B C D 1 1 a 1/1/2015 1 2 b 1/2/2015 1 7 c 1/3/2015 2 - a 1/4/2015 3 - b 1/5/2015 3 - c 1/6/2015 3 - a 1/7/2015 3 - b 1/8/2015 4 - c 1/9/2015 4 - a 1/10/2015 5 - b 1/11/2015 5 - c 1/12/2015 6 - a 1/13/2015 6 - b 1/14/2015 6 - c 1/15/2015 7 - a 1/16/2015 7 - b 1/17/2015 7 - c 1/18/2015

.

Sub LessSmartWay() Set t = Sheets("test") Set r = Sheets("select") Dim d As Integer Dim j As Integer d = 1 j = 2 Do Until IsEmpty(t.Range("A" & j)) If t.Range("A" & j) = t.Range("B2") Or t.Range("A" & j) = t.Range("B3") Or t.Range("A" & j) = t.Range("B4") Then d = d + 1 r.Rows(d).Value = t.Rows(j).Value End If j = j + 1 Loop End Sub

.

Sub FailedSmartWay() Set t = Sheets("test") Set r = Sheets("select") Dim d As Integer Dim j As Integer Dim i As Integer d = 1 j = 2 i = 2 Do Until IsEmpty(t.Range("B" & i)) Do Until IsEmpty(t.Range("A" & j)) If t.Range("A" & j) = t.Range("B" & i) Then d = d + 1 r.Rows(d).Value = t.Rows(j).Value End If j = j + 1 Loop i = i + 1 Loop End Sub

If Column B has 100, I do not want to click 100 times by using filter in column A. This is why I am trying to use macro. Thanks. — Feifei Zhang
– Feifei Zhang, Commented Nov 3, 2015 at 20:32
What should the output be? By your code it would have 7 lines all the rows with 1,2 or 7 in column A. Is this correct? — Scott Craner
– Scott Craner, Commented Nov 3, 2015 at 21:08

jeff carey · Accepted Answer · 2015-11-03 21:09:17Z

Reset the j value each time you iterate through the outer loop

Do Until IsEmpty(t.Range("B" & i)) ' Insert this line here j = 2 Do Until IsEmpty(t.Range("A" & j)) If t.Range("A" & j) = t.Range("B" & i) Then d = d + 1 r.Rows(d).Value = t.Rows(j).Value End If j = j + 1 Loop i = i + 1 Loop

Michael B. · Accepted Answer · 2015-11-04 01:44:10Z

A couple For/Each loops iterating over a Range. It just seems a bit cleaner.

Dim LastRowA As Long Dim LastRowB As Long Dim WB As Workbook Set WB = ActiveWorkbook Dim wks As Worksheet Dim wks2 As Worksheet Set wks = WB.Sheets("test") Set wks2 = WB.Sheets("select") LastRowA = wks.Cells(wks.Rows.Count, "A").End(xlUp).ROW LastRowB = wks.Cells(wks.Rows.Count, "B").End(xlUp).ROW Dim rowRangeA As Range Dim rowRangeB As Range Set rowRangeA = wks.Range("A1:A" & LastRowA) Set rowRangeB = wks.Range("B1:B" & LastRowB) ' keep track of our current line on second worksheet Dim currentEndingRow As Integer currentEndingRow = 1 For Each mCellA In rowRangeA 'Our nested loop, will cycle through each row in B once for every row in A. For Each mCellB In rowRangeB If mCellA.Value = mCellB.Value Then 'wks2.Cells(currentEndingRow, 1).Value = mCellA.Value wks2.Rows(currentEndingRow).Value = wks.Rows(mCellB.Row).Value currentEndingRow = currentEndingRow + 1 End If Next mCellB ' Move on to the next Row A after it finishes the last row in B. Next mCellA

Can you explain how mCellA (or mCellB) works? I did not see you define it? If I want to copy the whole row to the "select", I guess we need to tweak this line a little bit "wks2.Cells(currentEndingRow, 1).Value = mCellA.Value". How to change this? Thanks.
mCellA is defined within the For Each loop automatically, its definition is implied by just simply creating the For Loop. I commented out my original code before I saw you wanted the whole row copied over. What I have on there now will do the trick for copying the entire row, and not just a single cell.

Collectives™ on Stack Overflow

How to select rows that meet multiple criteria from a column?

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related