Tell macro to skip blank cells in a range and only select those with data?

I have a very simple macro that is copying data from a range of cells and the macro is called from a button on a different worksheet to the one from on where the macro runs.
I need a way of telling the macro to look at the cells in the specified range and only copy any data from formulas in the cells have that returned data but skip the cells with formulas that do not return any results (so when you are looking at the worksheet the cell looks empty even though it is not). The data in cells within the range may change depending on what a user (me) may enter before running the macro.

This is the macro

Please Login or Register  to view this content.

Here is an example of how the range it is copying can look like
Cell B1 = Red
Cell B2 = Yellow

Cell B4 = Green


Cell B7 = Gray

I need the macro to ignore the blank cells (the ones where the formulas do not return a result) so the macro results are:
Cell B1 = Red
Cell B2 = Yellow
Cell B4 = Green
Cell B7 = Gray

This is beyond my knowledge so can anyone kindly help?

Thanks in advance

Originally Posted by JieJenn

Maybe change your line to

Please Login or Register  to view this content.

Hi JieJenn

When I changed my line to what you suggest I get the following error:
run-time error '1004': no cells were found

Originally Posted by JieJenn

Maybe

Please Login or Register  to view this content.

Hi

Thanks for the suggestion which I have just tried but all that happens now is the macro copies the words "On Error Resume Next" to the clipboard...

You are must doing something wrong. It would be better if you can upload a sample.