Copy contents of ALL rows containing flag 2

[MeGustaXL]

Hi Folks,

I'm pretty sure this is one of those old chestnuts that's so simple I can't see the wood for the....etc

I have a table like this:

Line Desc Ind Time
1 Item 1 0 10:43
2 Item 2 0 10:43
3 Item 3 1 10:43
4 Item 4 0 10:43

etc…

The "1" flag in column "Ind" is set randomly by a program.

How can I step thru the table, find all occurrences of 1 in IND, and copy the entire row to another location on the same spreadsheet, appending to the data that's already there?

As I say, I'm sure this has been explained to me before, but it's lost for now :-(




Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[SkipVought]

"When I run the macro, it stalls at the Autofiltering part with a "400" error "

WHICH statement? You have TWO AutoFilter statements?

Skip,

_[red][/red]

 

[MeGustaXL]

It still hangs with a 400 error, and the cursor after End With..

Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[SkipVought]

That's not a good answer.

I need to know EXACTLY what statement it errors on.

Either hit Debug when it errors or Step thru the code to determine the statement.

Also, what does, "...and the cursor..." mean? That makes no sense.

Skip,

_[red][/red]

 

[MeGustaXL]

Sorry Skip,

I meant "It hangs with the following indications: (a) A 400 Error and (b) the cursor positioned after the 'End With' statement".

Anyhoo, I've Debugged and Step'd it, and the bit that's highlighted is:

Range("Z65536").End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlValues, _
        Operation:=xlNone, _
        SkipBlanks:=True, _
        Transpose:=False

With the error code

Run-time error '1004': Application-defined or object defined error

If I put On Error Resume Next at the start of the Procedure, it runs thru all the iterations, does the table of values and the chart.

Nothing, as in NO THING is copied across to column Z, even tho' the Status Bar shows the "X of Y Records found" message!


Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[MeGustaXL]

I've run it again on a new sheet with a 3 x 3 named table which generates random 1's in column C. I just put the Autofilter code in the Worksheet_Calculate event, thus:

With [EBS_table]
   .AutoFilter Field:=3, Criteria1:="1"
   .CurrentRegion.range(cells(2,1), cells(.rows.count, .columns.count)).Copy
    Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlValues, _
        Operation:=xlNone, _
        SkipBlanks:=True, _
        Transpose:=False
   .AutoFilter
End With

This works lovely, until all three rows have zero in column C. On such occasions, it copies all three rows across, complete with zeroes - Grrr!

Also, when the lines are copied over, it sometimes overwrites the last line or 2, until the copied table goes below the source table - is this because of the row hiding effect of the AutoFilter? If so, how to overcome it?



Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[MeGustaXL]

OK, I've fixed the

it sometimes overwrites the last line or 2

part by putting headings in E5:G5 so that the copied table starts below the source. This isn't a great pain in the sitting parts with a 3-row table, but with (potentially) 80-plus rows, I can see how it might be.

So I've put the headings on another sheet called "DataTable" and amended the code to read:

Sheets("DataTable").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial....

etc. That is fab, but it still copies all the rows if they are all not equal to 1 in column C (or Field 3)

Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[MeGustaXL]

I've tried replacing the filter criterion to NonBlanks, but it still takes the lot if they are all zero (or 2 or "DOGS" or anything except 1)

I'm beginning to want to change my handle to: "NoMeGustaXL" :-D

Chris

God knows as your Dognose - Bog Blast all of you! - Ozzy Osbourne

http://www.insys-ltd.co.uk

 

[SkipVought]

Do you nave the situation where you have BOTH numbers & test in the same column? Excel does not do very well with that situation.

In general, if a column has text values, then all numerica values need to be converted to text UNAMBIGUOUSLY.

Skip,

_[red][/red]

 

[MeGustaXL]

Sorry Skip, I'm in UK and I went home before you posted.

The bit about DOGS was just me trying to show that it doesn't make any difference what the "non-1" result is.
No, the contents of the tested column are all formulas of the form =IF(CellA >= CellB,1,0).

Anyway, I've FIXED IT! YAAAaaayyy!!

1. The table being filtered had (a) no headers and (b) non-table cells immediately above it. once I'd given the columns names, and inserted a blank row between the table and the other cells, it nearly worked.

Cell H5 counts the number of 1's in the tested column, so I added an If to the AutoFilter snippet to miss it if they're all zeroes:

[red][b]If [h5] > 0 Then[/b][/red]
        With [EBS_table]
    .AutoFilter Field:=5, Criteria1:="1"
   .CurrentRegion.Range(Cells(2, 1), Cells(.Rows.Count, .Columns.Count)).Copy
    Sheets("DataTable").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlValues, _
        Operation:=xlNone, _
        SkipBlanks:=True, _
        Transpose:=False
   .AutoFilter
End With
    Application.CutCopyMode = False
[red][b]End If[/b][/red]

That works like dream

Thanks for your patience and help Skip, as always, you inspired me to RTFM and look for the solution by using "The Force" aka Debug and Step

Have another star, Mate.



Chris

God knows as your Dognose - Bog Blast all of you! - Ozzy Osbourne

http://www.insys-ltd.co.uk

 

[SkipVought]

Then this may become your light sabre...

How to use the Watch Window as a Power Programming Tool faq707-4594

_{Snatch the pebble from my hand, Grasshopper.}

Skip,

_[red][/red]

 

[MeGustaXL]

Teach us you do; use my new Watch Window powers I will!

Chris

Why are there 5 syllables in the word "monosyllabic"? - Stephen Wright

http://www.insys-ltd.co.uk