Move all rows that satisfy a condition to another file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marcus Schöneborn - 13 Sep 2007 20:57 GMT

How to best move all rows that satisfy a given condition to another
file?

Like assume I have a boolean column, and I want to keep the TRUE ones in
my worksheet, but move the rows with a FALSE to another one.

Basically, it should look like a filter has been used, except that the
filtered out rows should not exist AT ALL (for example, sums shouldn't
include them). Instead, the rows that were filtered out should then
reside in a different XLS file as a backup.

My first idea was to make a DeleteRow(range) VBA function that does
range.EntireRow.Delete, and using that in a
=IF(condition;DeleteRow(1:1);0), but the Delete call seems to get
silently ignored when used from the very cell to be deleted. I know I
could make a VBA macro that iterates through all rows and does that,
but isn't there any simpler way?

But maybe the functionality already exists - and could somehow be done
with AutoFilters, like, making a copy of the worksheet that only
contains the VISIBLE cells?

Reply to this Message

JW - 13 Sep 2007 21:23 GMT

I have written a much more elaborate method for doing this with
AutoSave options, path locations, blah, blah, blah. But, here is a
quick, crude method of seperating the cells that contain the word
"False" in column 3 (C) into a new workbook. Very crude and untested,
but it should work for you after you make the necessary tweaks.
Sub foofer()
Dim shTarget As Worksheet, mainSheet As Worksheet
Set mainSheet = ActiveSheet
Worksheets.Add , ActiveSheet
Set shTarget = ActiveSheet
shTarget.Name = "FalseToMove"
With mainSheet
.Columns(3).AutoFilter 1, "FALSE"
.Cells.SpecialCells(xlCellTypeVisible).Copy
shTarget.Range("A1")
End With
mainSheet.AutoFilterMode = False
shTarget.Move
Set mainSheet = Nothing
Set shTarget = Nothing
End Sub
Marcus Sch?neborn wrote:

> How to best move all rows that satisfy a given condition to another
> file?
[quoted text clipped - 17 lines]
> with AutoFilters, like, making a copy of the worksheet that only
> contains the VISIBLE cells?

Reply to this Message

Bruce Sinclair - 17 Sep 2007 05:51 GMT

>How to best move all rows that satisfy a given condition to another
>file?
[quoted text clipped - 17 lines]
>with AutoFilters, like, making a copy of the worksheet that only
>contains the VISIBLE cells?

IIRC, a filter, then a copy and paste to another sheet work fine for this.
It may be XL version dependent however. :)

Reply to this Message

Rate this thread: