Copying only rows with specific value in an indicator column to a new sheet

[d0rian]

See image below -- should be self-explanatory.

I have a table of several thousand rows and I need to copy data ONLY for rows where the value in column E is "x" over to another sheet.

I can't screw with anything to the right of column E on either Sheet 1 (source) or Sheet 2 (destination) because there are unrelated formulas in cols F and onward, so I can't delete entire rows or anything like that...

 

[Michael M]

so you need to copy only data or are there formuals and / or formats as well ???

 

[Michael M]

Try this....
Autofilter would have been quicker, but having "several thousand rows" might compromise autofilter

Sub mm1()
Dim lr As Long, r As Long, nr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
    nr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
        If Range("E" & r).Value = "x" Then
            Range("A" & r & ":E" & r).Copy Sheets("Sheet2").Range("A" & nr)
        End If
    Next r
End Sub

 

[Trebor76]

Hi d0rian,

Here's a non-macro way that uses the filter as Michael suggests:

1. In Sheet1 put a filter across A1:E1
2. Filter for x in Col. E
3. Highlight the entire filtered range from A2
4. Press F5 and then click the Special button
5. Click the Visible cells only radio button and then click OK
6. Copy the selected range (Ctrl + C) to the clipboard
7. Select cell A2 on Sheet2 paste the contents of the filtered data (Ctrl + V)

Hope that helps.

Robert

 

[My Aswer Is This]

Try this:

Sub Copy_Rows()
'Modified 10/23/2018 8:10 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Cells(Rows.Count, "E").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "E").End(xlUp).Row + 1
For i = 2 To Lastrow
    If Cells(i, 5).Value = "x" Then Cells(i, 1).Resize(, 5).Copy Sheets(2).Cells(Lastrowa, 1): Lastrowa = Lastrowa + 1
Next
Application.ScreenUpdating = True
End Sub

 

[Akuini]

Here's another way using macro without looping directly in range, so it should be faster:

[COLOR=blue]Sub[/COLOR] a1075191a[B]()[/B]
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1075191-copying-only-rows-specific-value-indicator-column-new-sheet.html[/COLOR][/I]
[COLOR=blue]Dim[/COLOR] va[B],[/B] txt [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
rr [B]=[/B] Range[B]([/B][COLOR=brown]"E:E"[/COLOR][B]).[/B]Find[B]([/B][COLOR=brown]"*"[/COLOR][B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
va [B]=[/B] Range[B]([/B][COLOR=brown]"E1: E"[/COLOR] [B]&[/B] rr[B])[/B]
    [COLOR=blue]For[/COLOR] i [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B])[/B]
    [COLOR=blue]If[/COLOR] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] [COLOR=brown]"x"[/COLOR] [COLOR=blue]Then[/COLOR]
    txt [B]=[/B] txt [B]&[/B] [COLOR=brown]"A"[/COLOR] [B]&[/B] i [B]&[/B] [COLOR=brown]":E"[/COLOR] [B]&[/B] i [B]&[/B] [COLOR=brown]","[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]Next[/COLOR]
   
txt [B]=[/B] Left[B]([/B]txt[B],[/B] Len[B]([/B]txt[B])[/B] [B]-[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
Range[B]([/B]txt[B]).[/B]Copy Sheets[B]([/B][COLOR=brown]"sheet2"[/COLOR][B]).[/B]Range[B]([/B][COLOR=brown]"A2"[/COLOR][B])[/B]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[Michael M]

@robert
I thought the filter may have failed given the "several thousand rows"
But it's good to have the option anyways...

 

[Trebor76]

Hi Michael,

I actually didn't see that when I first looked at the post. Thanks for pointing it out and as you say there's now a VBA and non VBA solution that may help others.

Cheers,

Robert

 

[Rick Rothstein]

If the values in Column E are constants, not formulas, then this macro should also work...

[table="width: 500"]
[tr]
	[td]Sub CopyRowsWithXinColumnE()
  Intersect(Sheets("Sheet1").Columns("E").SpecialCells(xlConstants).EntireRow, Sheets("Sheet1").Columns("A:E")).Copy Sheets("Sheet2").Range("A1")
End Sub[/td]
[/tr]
[/table]

 

[d0rian]

Thanks for the replies. The solutions that use a repeating process (Michael / My Answer Is This) > it seems to go very slow...in fact, Excel just seemed to hang for about 45-second with a spinner icon, until I killed the macro with ESC, and went to Sheet2 to see that it had gotten through around 750 (of the ~9,000 rows)...so that's prob going to be too slow a solution.
(Aquini, your code gae me a runtime error re: the line: Range(txt).Copy Sheets("sheet2").Range("A2") **Method 'Range' of object '_Global' failed )

Trebor's filter / copy visible process works somewhat quickly...I guess I could create a macro that goes through those steps to achieve what I want.

**EDIT: Michael you say "I thought the filter may have failed"...can filters get buggy/unreliable with thousands of rows? That would render that solution a non-starter for me...