Macro to copy/paste excluding blanks

[mmdecarl]

Hi All,

I've created a simple macro to copy a range from sheet 1 to sheet 2 that is attached.

I am having difficulty changing the code so that it excludes the blank rows in the range. I would like to copy range("a:d") from sheet 1 and paste it to sheet 2 without the blank rows. Any help would be very much appreciated.

thank you,

Mark

COPYMACRO.xlsm

[AB33]

It would be efficient to copy all and then delete the empty rows.

Please Login or Register  to view this content.

[mmdecarl]

Hi AB33,

Thanks for you response. This code worked well for me except when I moved a step further and included an If statement in my sheet.

Some of the "blank rows" i have will be "" and will not be read as blank cells. Is there a way to remove the rows containing "" ? sorry to make this confusing.

Mark

COPYMACRO.xlsm

[mmdecarl]

Sorry if that last post was confusing. The blanks rows i would like to have deleted actually contain "" and are not ready ASBLANK by excel. Is there a way to tell excel to remove the entire row if "A" contains "". Hope this clarifies my last post.

the attachment from my above post is an updated workbook containing the code you created for me.

Thank you again,

Mark

[AB33]

Mark,
In that case, you have two options. You can either do a looping for each cell and exclude any blanks, but it is slow and time consuming if you have large data. The other option would be to use filter and runs faster. You can include/exclude cells which are blank, or contains some words.