Move all rows that satisfy a condition to another file

  • Thread starter Thread starter Marcus =?iso-8859-15?Q?Sch=F6neborn?=
  • Start date Start date
M

Marcus =?iso-8859-15?Q?Sch=F6neborn?=

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?
 
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
 
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?
Click to expand...

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