Save Macro: filter adds an empty row

[MarcoNecroX]

Hello dear Excel community.

First I want to thank the Excel forums for always being the first ones to send me an email for my Birthday. Usually I'm eagerly waiting for messages as soon as the clock hits 0:00, I tend to not receive any except for Excel community, so it's a great relief. Big thanks to whoever is taking the time to send me those messages.

Anyways, I have an excel macro which converts my sheets to TXT format when I press a special key combination. These TXT files are data used by my Application.

Please Login or Register  to view this content.

However, when I have a Data->FILTER applied to my sheet, upon saving, Excel adds an extra blank line at the end of the TXT file. This causes my app to break in complete anger.
I am curious how can I detect if the last line is empty, and remove if it is, when generating the TXT file.

Very much appreciated. Thank you EXCEL COMMUNITY.

[xladept]

Maybe you can turn off the filter before you save the book:

Please Login or Register  to view this content.

[MarcoNecroX]

Thank you very much for your response. Unfortunately, the filter is disabled but there's still an empty row at the end.

[xladept]

Does that mean it wasn't the filter? The copy seems unusual, have you tried it without the copy?

[MarcoNecroX]

The reason to copy the current sheet to a new one is also because it's backed up (rather than backing up the entire book)

When I do this

Please Login or Register  to view this content.

There is an extra row at the end. However, if I disable the filter *before* cloning the sheet, like this:

Please Login or Register  to view this content.

No extra row is added. So cloning a sheet with an active filter is what creates this extra row.


If I were to do this approach (unlike removing last row when there is) would it be possible to turn the previous filter back on (only if there was a filter to begin with)?

Thanks again for your time.

[xladept]

But you just saved the workbook so why do you need a copy?