How can I copy data in a column until the next cell is ""?

Hi guys,

I have written this code that draws data from one sheet to another (Please see the code below). I use a do until loop to do this. But I have 2 problems with it.

1. I have a formula commanding to return "" in case of an error in the original data and I don't want the code to draw the empty data (meaning the ones with ""). Hence "End(xlUp).Row" is not cutting it for me. How can I achieve this?
2. In the code, the copy and paste selection ranges are supposed to go to the end but it just copies the first row of the selected columns and that's it.

It's crucial for me to stop copying if the cell is "" and not absolute blank because I will use the copied data to create a pivot table. If the data with "" formula is there, it will pick it up as data and include it in the table, ruining my slicers.

Thank you very much in advance!

Please Login or Register  to view this content.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)