[Article & AddIn] How to paste into a filtered range

[Andreas Killer]

If you have looked at the code in the AddIn, you will probably notice that it is more extensive than expected.
Unfortunately it is not that easy in Excel to solve this task.

And I have to be fair to say that I don't really do a Paste as it is possible in Excel, because that would also have to include formulas, colors, fonts, borders and more. I limit exclusively to values and, if necessary, number formats, even if more would be possible.

So why all this effort? Couldn't it be easier?

Well, a big advantage of this AddIn is that it works not only with cells, but also with other sources, for example a website (if it defines a table). This was my starting point in the past, I often had to copy data from websites / PDF files. For me it was worth the effort.

And easier... if you accept detours and peculiarities... perhaps. Many programmers have attempted this task... judge for yourself. And by the way, this code wasn't like this from the beginning, I also learned a lot over the years.

Let me first point out the problems we have to deal with, and then let's look at the details of the solution.

The first problem is where do we get the data that we want to write to the target cells, there are 2 possibilities: If cells have been copied, then the source cells themselves or, if they are not accessible, the clipboard.

When you start with Excel, everything seems to work very simply and logically at the beginning. But after a time, you come across curiosities that you can't explain.
Above all, it's a date that suddenly and unexpectedly appears on the screen, even though you didn't want it to be.

Try it out for yourself, open Excel and write 1-2-3 in a cell. To us as humans, this looks like a bulleted list, but in Excel, it becomes a date.
Why is that? This brings us to the problem of data conversion and the automatisms in Excel.

Inside the clipboard are 2 structures that we use for our purpose the HTML Format and XML Spreadsheet.

After that, we have to deal with how to recognize the visible cells in the filtered range and how to write the data only to them.
If you now consider that the range to be copied and the target range can be different in size, you know it’s not easy. Let's add ranges of different sizes to our list.

These are the big topics on our list, and each topic has some details that we need to discuss if we want to understand what happens in Excel resp. how Excel works.

I divide the topics into separate posts, then it will be easier for you later when you want to read again.

Take a big cup of tee or coffee and let's get started.