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...

 

[Rick Rothstein]

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...

Did you see my posting (Message #9 ) yet... our postings appear to have crossed at about the same time?

 

[d0rian]

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

[TABLE="width: 500"]
<tbody>[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]
</tbody>[/TABLE]

Hm, at first blush this seems to work very well...small hiccup is that yes the values in col E are constant, BUT they are the product of a formula that returns either "x" or null("")...so the "empty" cells aren't really 100% empty (and I suppose still register as 'constants' for the purposes of that code(?)). Can get around pretty easily by having col E return either "x" or "zz" and then running a Find/Replace on that column to replace "zz" with "", which effectively clears the cells...

Thanks, though -- this works extremely well.

 

[Rick Rothstein]

Hm, at first blush this seems to work very well...small hiccup is that yes the values in col E are constant, BUT they are the product of a formula that returns either "x" or null("")...so the "empty" cells aren't really 100% empty (and I suppose still register as 'constants' for the purposes of that code(?)). Can get around pretty easily by having col E return either "x" or "zz" and then running a Find/Replace on that column to replace "zz" with "", which effectively clears the cells...

Thanks, though -- this works extremely well.

I specifically said "If the values in Column E are constants, not formulas..." because values returned by a formula are not constants (they can vary if the values in the cells they are dependent on vary, hence, they are not constant). If you have your formula return "x" or 0 (the number zero, so no quote marks around it), then this modification of my macro should work...

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

 

[d0rian]

I specifically said "If the values in Column E are constants, not formulas..." because values returned by a formula are not constants (they can vary if the values in the cells they are dependent on vary, hence, they are not constant). If you have your formula return "x" or 0 (the number zero, so no quote marks around it), then this modification of my macro should work...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CopyRowsWithXinColumnE()
  Intersect(Sheets("Sheet1").Columns("E").SpecialCells(xlFormulas, xlTextValues).EntireRow, Sheets("Sheet1").Columns("A:E")).Copy Sheets("Sheet2").Range("A1")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Yup, thanks Rick. And I did understand your initial post about constants and maybe I wasn't clear: column E were constants. They weren't formulas. But the "blank" cells weren't really blank because on another sheet, I'd had a formula return either "x" or "", and then had copied the values of that formula-containing column to column E of this sheet...so they were hardcoded values (what I understand to be a 'constant')...except the "empty" rows were hard-coded as null(""), and not truly blank, which apparently was a problem for the original code. I'll give your 0 solution a whirl, though you've already gotten me far enough to get to the finish line, thanks!