VBA - Copy and paste only the cells with values

[Frankinstyyn]

Hi Everyone,

Hoping for another gem to help me.

I have data in Cells K:2 to L:13 in which the length will never change but the values in column L will - This is on sheet "Aged Grid"

Currently the data copies across to sheet "DeleteZeros" and then removes the blanks perfectly. What I now need is on the "DeleteZeros" sheet a macro that only copies cells with values from B2 all the way down to the next blank cell in column C, then pastes them back into the "Aged Grid" sheet under the next blank cell in column B - I've attached the spreadsheet so this all makes sense.

Thank you in advance!!!!

Frank

[alansidman]

I have data in Cells K:2 to L:13 in which the length will never change but the values in column L will - This is on sheet "Aged Grid"

There is no data in these cells on this sheet

What I now need is on the "DeleteZeros" sheet a macro that only copies cells with values from B2 all the way down to the next blank cell in column C

In your example are we copying B2:C7 even though there are errors in two of the cells?

then pastes them back into the "Aged Grid" sheet under the next blank cell in column B

Paste to B26?

Please clarify as I am a bit confused by your explanation and the worksheet shown. They don't quite seem to correlate.

If you use your worksheet and explain which cells in your example and where they should go it would help to understand what you are asking.

[Frankinstyyn]

Hi Alan,

Thanks for getting back to me.

Apologies it's unclear. I've deleted some data as it had personal stuff.

To clarify:

The errors don't really matter too much - they've been sorted now though.

K:2 to L13 have data but they're already copied across.

To copy back to Aged Grid, in this case it would be B4

I've also updated the spreadsheet with instructions, I hope this helps as well.

I hope that makes more sense.

[Frankinstyyn]

Adding updated spreadsheet

[alansidman]

I am still unsure about your request. Do you wish to copy from "Delete Zeros" Range B2:C5 to "Aged Grid" Range B4:C7. Apologize if I am not getting it but your instructions are not crystal clear. As I indicated earlier, specifics are the key to an answer.

[Frankinstyyn]

I am still unsure about your request. Do you wish to copy from "Delete Zeros" Range B2:C5 to "Aged Grid" Range B4:C7. Apologize if I am not getting it but your instructions are not crystal clear. As I indicated earlier, specifics are the key to an answer.

Thanks again for replying.

Apologies if i'm not quite getting it across to you.

So from "Delete Zeros" the range will always start with B2 but the list could be longer or shorter. This is where I need the macro to pick up only the cells with values. Then it will paste into "Aged Grid" always going to B4, that part is easy,

It's getting the macro to pick up only the data that shows in Delete Zeros from B2.

It will only be in columns B and C but rows can be from 1 to 12.

Hope that clears things up. Please ask for anything else. Thanks again

[alansidman]

Based upon your explanation and my understanding, this should be what you are looking for:

Please Login or Register  to view this content.

[Frankinstyyn]

Based upon your explanation and my understanding, this should be what you are looking for:

Please Login or Register  to view this content.

Thanks for helping but it keeps coming back with Run time error '1004' "We cant do that to a merged cell"

The merged cells I have were originally in column B but now I've taken that out it still doesn't work.

Not sure if you can help further? Thanks in advance.

[alansidman]

When you get the error, what line of code is highlighted. Click on debug.

The merged cells I have were originally in column B but now I've taken that out it still doesn't work.

What does this mean. What happens? Does not work does not help me to help you. There were no merged cells in your example that conflicted with the code. Is your example not representative of the actual worksheet?

Merged cells and VBA do not like each other. Most VBA developers avoid merged cells like the plague.

[Frankinstyyn]

When you get the error, what line of code is highlighted. Click on debug.
What does this mean. What happens? Does not work does not help me to help you. There were no merged cells in your example that conflicted with the code. Is your example not representative of the actual worksheet?

Merged cells and VBA do not like each other. Most VBA developers avoid merged cells like the plague.

I tried again on a clean sheet with your clean code and the merged cell error didn't show.

When running, the dates copy across perfectly, in the correct place but the values do not go across.

It should look like:

April 1
July 2
August 26
September 13

All i'm getting is:

April
July
August
September

[alansidman]

That is because, I was unsure what you wanted and guessed and only did a copy/paste on the first column. I am not a very good mind reader.

Change the code to the following to include the second column.

Please Login or Register  to view this content.

[Frankinstyyn]

I've tried it on it's own and it's absolutely perfect!! So thanks so much for that.

The only problem now is when it's called from my other macro, nothing appears in column B or column C in "Aged Grid"

No errors, and the message box you created appears as well I guess my other code affects yours? I've popped it below

Please Login or Register  to view this content.

[alansidman]

What happens when you step through the code. Open your code in VBE. Click on Debug. Step through the code line by line (F8) and see what is happening to the highlighted line when you put your cursor on each line as it steps through. This will tell you exactly what is happening. I cannot see any issues.

[sanram]

Try this :

Please Login or Register  to view this content.

[Frankinstyyn]

What happens when you step through the code. Open your code in VBE. Click on Debug. Step through the code line by line (F8) and see what is happening to the highlighted line when you put your cursor on each line as it steps through. This will tell you exactly what is happening. I cannot see any issues.

Thanks for replying again, i've tried to follow that and I too have not seen any errors.

When putting my cursor on the lines should anything pop up other than things like =4165

I followed all the lines and none of them skipped except the two below

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Frankinstyyn]

Try this :

Please Login or Register  to view this content.

Thanks for replying!

I tried this and nothing happened. Nothing was deleted, nothing was pasted.

I then put a msgbox to check if it completed and it did.

[sanram]

Thanks for replying!

I tried this and nothing happened. Nothing was deleted, nothing was pasted.

I then put a msgbox to check if it completed and it did.

This code is copying only from DeletedZero to Aged Grid. Do you want the code to copy Aged Grid to DeletedZeros without 0 values also?

[sanram]

Try this:

Please Login or Register  to view this content.

It will copying data without 0, then placing it to Aged Grid.