vba copy paste only half working

I'm running the following script on a button push:

Please Login or Register  to view this content.

B and J are columns with descriptors, D is user entered data as a numerical value and L is formulas providing. The formulas all have a =IF(cell<>0,formula , "") so they are blank until information is input elsewhere.

The first part works perfectly B41:D73 are copied to sheet2 and any blank cells in column D result in the entire row being deleted so only cells with a value and their accompanying descpritors are left.

The second part doesn't want to play ball. It copies over J30:L56 to sheet3 but for some reason it doesn't carry out the final part and delete the rows with a blank in column D (or it does and doesn't recognise them as blank.) I'm assuming it's something to do with the source being formulas but I thought the paste special values would mean this isn't an issue.

[B]Moderator's Edit: Use code tags when you post code in your post. To do so either select your code and click on the # symbol above or type [noparse][code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] after it. [/B]

It worked fine. Do you have a sample file that you can attach?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

On which button you want to run the code? Also the code is not in your file.

I tried it in several ways but the sheet is really acting weird. Its all because of the formulae in sheet1. But the pastespecial should actually take care of it.

I used a error handler and tried to trap the error so i can get an error description. It says "No Cells found". Its not treating those cells as blank.

I then tried using a loop at the end of your code to go through each row of sheet3 - Your updated code -

Please Login or Register  to view this content.

This seems to work.

Thank you very very much for this. It works a charm. I have a presentation on it at work tomorrow and was starting to panic.

Hopefully the rest of what I need to do should be straight forward!

Edit: just tried a couple of test runs and it is almost working as desired. For some reason the new code deletes/doesn't pick up the first entry copied over from sheet1 column L