Copy to next available row leaves blank row

[vonrainer]

Hello,

I have set up a macro to filter entire row to another sheet if certain criterias are met.
This works like a charm, BUT:

When it pastes the row to the destination sheet, it always leaves a blank row between it and the previous (in destination sheet).
Is this possible to fix somehow?

Example:
In destination sheet the next avilable row is 11
However, the content is added on row 12, leaving row 11 blank.

Here is the code:

Please Login or Register  to view this content.

[AB33]

It is because you have data which appears to be blank to your eyes, but may not be blank to VBA.

Please Login or Register  to view this content.

Pastes in to next empty row

[vonrainer]

Ok.

Is it then possible to add something like:
- Delete the row above the one that was pasted to?

Example:
If it paste it to row 11 (leaving 10 blank for my eyes), delete row 10.

[Richard Buttrey]

Have you checked that A11 on the Norway sheet is in fact blank? It may contain a space or some other non visible character.

If not upload the workbook so that we can see the problem in context.

A few observations.

You don't Dim LR1 which suggests you don't have "Option Explicit" as the first line and in the VB Tools Options editor tab you don't have 'Require Variable Declaration' ticked, which is always good practice,

Why do you have two filter criteria both for Norway? Won't one do?

Don't forget that if you use Advanced Filter that you can avoid having to do an AutoFilter and then a Copy Paste. Personally I generally prefer the advanced filiter since the code is shorter and it just seems more elegant.

If you modify the
.SpecialCells(xlCellTypeVisible).EntireRow.Copy
to
.SpecialCells(xlCellTypeVisible).Offset(1,0).EntireRow.Copy

you will avoid having the need to delete the row of column labels.

[vonrainer]

Thank you very much Richard!

You solved the initial question! It now works like a charm

[Richard Buttrey]

Glad to have helped and thanks for the rep.