Advanced Filter Copy Paste Blank Line Hell :(

[Guest]

Here is a problem that is driving me nuts..

A workbook imports data into Sheet1, and this data is analysed using an advanced filter with criteria that are inserted above the data (once imported, blank lines are inserted and a number of criteria sets are pasted as headers into rows 1-7, with the advanced filter called after ie

In this example, header rows and criteria are pasted in rows 1-7, but there is only criteria in A2-K4 (hence the values)

Range("A7:K50000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange
:=Range("A2:K4"), Unique:=Fals
Selection.SpecialCells(xlCellTypeVisible).Selec
Selection.Cop

Then, in another empty sheet, result is pasted..
Range("A1").Selec
ActiveSheet.Past

Except that the new page is filled with hundreds of empty lines after the data..
Using blank row (based on empty column "A") doesn't help...ie

Selection.SpecialCells(xlCellTypeBlanks).Selec
Selection.Clea
Range("A1:A65535").SpecialCells(xlCellTypeBlanks).EntireRow.Delet

Also, the data seems to occupy too many columns aswell (although i'm sure this is explained by the fact that the imported data uses columns A -> R, but the data is stripped down to A -> K
Hunches are a solution has something to do with pdating active range (although this is Excel XP not 97), and maybe something to do with the "CellTypeVisible" including all blank rows - maybe an alternative operand?
Any help would be greatly appreciated - the processed workbook is 20x bigger !!

Thanks in advance

Mike

 

[Anders S]

Hi Mike,

I don't quite follow or where the blank lines come from, but to reset the last
cell after deleting, the file has to be saved (and in earlier versions closed
and reopened)

'-----
Sub test7632()
Range("A1:A65535").SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'your code
ActiveWorkbook.Save
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
End Sub
'-----

HTH
Anders Silven

Here is a problem that is driving me nuts...

A workbook imports data into Sheet1, and this data is analysed using an

advanced filter with criteria that are inserted above the data (once imported,
blank lines are inserted and a number of criteria sets are pasted as headers
into rows 1-7, with the advanced filter called after ie,

In this example, header rows and criteria are pasted in rows 1-7, but there is

only criteria in A2-K4 (hence the values):

Range("A7:K50000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Range("A2:K4"), Unique:=False
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Then, in another empty sheet, result is pasted...
Range("A1").Select
ActiveSheet.Paste

Except that the new page is filled with hundreds of empty lines after the data...
Using blank row (based on empty column "A") doesn't help...ie,

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Clear
Range("A1:A65535").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Also, the data seems to occupy too many columns aswell (although i'm sure this

is explained by the fact that the imported data uses columns A -> R, but the
data is stripped down to A -> K )

Hunches are a solution has something to do with pdating active range (although

this is Excel XP not 97), and maybe something to do with the "CellTypeVisible"
including all blank rows - maybe an alternative operand??

 

[Guest]

@Anders S - thanks for your reply. You and me both ! i'll give another example..

One page has a list of ID numbers in "A", and other data in other columns. An algorithm runs through the data and deletes most of the columns, often at a magnitude of starting with 10000 rows and ending with 500, for example
An advnaced filter is then used to produce a list of unique IDs..

Range("A1:A50000").AdvancedFilter Action:=xlFilterInPlace, Unique:=Tru
Selection.SpecialCells(xlCellTypeVisible).Selec
Application.CutCopyMode = Fals
Selection.Cop

This is then pasted into a new empty page using ActiveSheet.Past

If I look at the new sheet, it has thousands of rows (all blank after the 100 or so unique IDs) - If I hit print, I get a documet of 400 pages instead of just 4 :
I can, of course, circumvent the print side of things by detecting the last used row and creating a print area, but the file size of this document is huge - and i'm sure it doesn't have to be..

I'm stumped. I'm sure I found a way around this once before... but can't seem to find the answer now

TIA

Mike

 

[Guest]

I should add that the first advanced filter job changes changes criteria-dependent cells colorindex... again selected with xcCellTypeVisible, as below..

Selection.SpecialCells(xlCellTypeVisible).Selec
With Selection.Interio
.ColorIndex =
.Pattern = xlSoli
.PatternColorIndex = xlAutomati
End Wit
Application.CutCopyMode = Fals
ActiveSheet.ShowAllDat

This seems to colour remaining rows on the page, even though they do not fit the criteria (ie there must be an entry in column "A" - maybe somehow these interfere later ?? It seems to follow on from another issue, where I use

It seems that a blank cell is considered to contain the number "0" - so a IsNumeric will give "true". Maybe this value of "0" means the cell isn't "empty" to the advanced criteria filter (I use <> as criteria to consider rows with an entry in "A"
Many thanks so far

Mike

 

[Guest]

OK. Solved this one... this odd behaviour occurs if you use an advanced filter using xlFilterInPlace followed by selecting cells using Selection.SpecialCells(xlCellTypeVisible) - for some reason it sees fit to include hundreds of lines.

The solution is to not be so longwinded. I was using an advanced filter to filter data in-line, then copy the visible cells and paste into another sheet... when i should have used the xlFilterCopy action, and specified CopyToRange:= to point to where i wanted the results to go... that's it ! No more rows++

HTH someone else in the future - couldn't find much documentation on the advanced filter setings... so the usual right-click - what options are there - "that sounds helpful" - trial and error job to the solution...

 

[Anders S]

Hi Mike,

Maybe SpecialCells(xlCellTypeVisible) is the problem. Whatever xlCellTypeVisible
yields, you don't want it.

Try
range("A7").CurrentRegion.Copy
or
range("A7").CurrentRegion.Select
and see what you get.

To make the Advanced Filter copy the filtered data into another location is a
nice shortcut if that's what you want.

Regards
Anders Silven

OK. Solved this one... this odd behaviour occurs if you use an advanced filter

using xlFilterInPlace followed by selecting cells using
Selection.SpecialCells(xlCellTypeVisible) - for some reason it sees fit to
include hundreds of lines..

The solution is to not be so longwinded. I was using an advanced filter to

filter data in-line, then copy the visible cells and paste into another sheet...
when i should have used the xlFilterCopy action, and specified CopyToRange:= to
point to where i wanted the results to go... that's it ! No more rows+++

HTH someone else in the future - couldn't find much documentation on the

advanced filter setings... so the usual right-click - what options are there -
"that sounds helpful" - trial and error job to the solution...