Filtered data to exclude header row

[Sgligori]

Hi All!

I'm trying to update my code below to only copy the visible cells and exclude the header row.

When there is criteria that meets the filter requirements, the code works fine (header is not copied), but when there is no criteria that meets the filter requirements, the header row gets selected. Why is this? And how am I able to update my code to exclude the header row?

Please Login or Register  to view this content.

[AB33]

Short and dirty way is to put in on error resume next.
OR

Please Login or Register  to view this content.

This will make the code to skip and not copy any data if the criteria are not met.

[BadlySpelledBuoy]

I occasionally deal with this by adding one to LastRow.

Please Login or Register  to view this content.

BSB

[Sgligori]

Thanks, but the code is not working for me.

LastRow isn't the count of the row, but the Row number it's on. So, for example, if I had my data filtered for 3 only rows, Row 7, 14, and 29, the LastRow=29.

[AB33]

I should have include equal to
If LastRow >= 2 Then
When you filter data and criteria are not met, there is no visible cells to copy. In such situation, the code will not copy, nor error. If there are filtered cells and count of rows are >=2 the code copies the filtered cells.
Difficult to show you in text format, please attach the code with the sample and will correct it.

[Sgligori]

That worked. Thanks, AB33!

[Alf]

Assuming you use autofilter have you tried using the Offset function?

Please Login or Register  to view this content.

Alf

[AB33]

ALf,
A2 will do.
The issue is if the criteria fail, filter copies the header. I would normally expect to get an error if criteria fail, as there are not visible cells to copy, hence for skipping the error.

Filter is very clunky function and seems to have some bugs (From my experience).

[Alf]

With headings in row 1 and no data found using autofilter settings it seems like the Offset(1, 0) command overrides the SpecialCells(xlCellTypeVisible) setting so a copy command it will happily copy 1 empty row i.e. row 2 in a case like this.

Alf

[AB33]

What happens if the criteria are not found? Will the offset still works? How can VBA copies data if it could not find any visible cells, hence the error? The offset is just a way of skipping the first cell in the range.

[Alf]

Step through the macro "Tester" and you will see what I mean. The criteria in filed 1 is "paul" a value not found in data range so the offset command copies the
first row outside the filtered range i.e. row 6 which makes sense as this is outside autofilter range and the "SpecialCells(xlCellTypeVisible).Copy" command can happily
be ignored.

Alf

[AB33]

Alf,
Okay, it is another way of skipping the error.
If I want to copy, including a header, I have changed the offset to 0
ActiveSheet.AutoFilter.Range.Offset(0, 0).SpecialCells(xlCellTypeVisible).Copy
If the match is found, it works and does not make any difference, but imagine there is no match and I do not want to copy the header. Mine skips it, but your code copies the header.

[Alf]

Funny I thought the OP's problem was

to update my code below to only copy the visible cells and exclude the header row

that is why one would use the Offset(1, 0). Cant see why one would use Offset(0, 0) unless one wanted to include header row.

Alf

[AB33]

Yes, you are right as far as the OP is concerned, "BUT" and then I do not want to drag the issue in to Ifs and Buts and I rest my case.