Macro for filtering empty cells and pasing result to another location

[Kruno]

Hi,

In Calc I have a list of people and keep track who is showing up for every working day of a month. Managed to use formulas to dynamically locate desired column based on current month and date, so I’m presented with needed information, but polluted with blank cells.

I need to copy that over to Writer for making a report and problem is that empty cells get copied too so I need to manually clean Writer document on daily bases.

Where I could start to learn macro so I can get desired range, filter out empty cells and paste only non-empty cells to a different location on same sheet.

Need information and explanations about particular functions and methods needed to be used. Big HowTos or Getting started Guides won’t do me much good as then I need to learn it all before I can get a grip about what I need to know for this particular task. I would rather start from what I need to know right now and expand from there.

Hopefully this makes (at least some) sense.

Thanks

[SM_Riga]

Hello @Kruno

Please find Demo spreadsheet attached. The file contents macro in Basic, which shows you the principles of getting the desired range, creating filter descriptor and conditions and then copying the result in another place on the sheet. The code shows the basics and I also tried to comment each step to describe it as much as possible. As I am not aware of the real structure of your data, you definitely will need to adopt these lines for your task, feel free to ask help!

P.S. Here is a good topic about filtering data using formulas. Maybe we can try to help you to modify them for your needs. And the suggestion by @PhLo is also good - it takes only a few seconds to reset and reapply AutoFilter, while cleaning up Writer document is definitely longer.

[Kruno]

Thank you very much! I did some macro programming in Google Sheets but they documented everything very nicely. It’s very hard to find needed information when it comes to LO. I’m looking at your spreadsheet right now and demo does what I need I’ll inspect how your macro is done and report back this evening or next morning to provide feedback and accept the answer. Thank you and @PhLo.

[Kruno]

Your example works only if content in column A is direct input, but my ‘column A’ holds information that’s result of array formula. Think script is not able to interpret that. Think that adding functionality to first hard-copy range from column A to column B and then filter by column B would do the trick. But thanks, you gave me what I needed, rest is for another question and for me to investigate further.

[Kruno]

Recorded a Macro that copies content that’s result of array formula to helper column and modified your script to use that at starting point - solved! Thanks!

[SM_Riga]

You are welcome, @Kruno !

[PhLo]

Your situation might be more complex than the auto-filter can answer, especially if it involves multiple columns of connected info. But if not, it could help you or someone else.

Auto Filter tool in Calc. With the top cell of your desired range selected (usually a column heading above the values), click this tool

This is what I started with, notice the blanks between the letters:

If you click on the Auto Filter tool, you should see a dropdown on the column heading. Click it and uncheck “Empty” to make empty rows disappear temporarily. You can remove the filter to restore the blanks later.

Now all the blanks are gone (hidden, not deleted). If you select all the filled-in values and copy/paste them, the blanks should not be copied along with.

[Kruno]

Thank you for the input, but filter is a no-go. Content is static range of dynamically populated cells - they change every day within the range and filtering is not showing the change. I would need to apply filter every day, which is the same as cleaning up Writer document. What I actually want is copy of that range but without empty cells. Maybe can be done with formulas INDEX, MATCH, ROW, MIN, MAX… don’t know. Some did it without macro, but I’m not able do modify formulas to get what I need

[PhLo]

I kinda figured you had good reason to ask specifically about macros. I have learned a bit about them in the last month but mostly in the context of Base, not Calc. Sorry I’m not much else help. I also had difficulty finding good documentation. There are macro experts on this forum. Hopefully one of them will chime in for you.