Ignore blanks in Compile workbooks code

Hi Michael and welcome to ExcelForum,

The following will find all the non-blank cells in a Range. A cell that contains a SPACE is NOT BLANK. The code is a lot simpler if there are no formulas or no constants (i.e. values in the cells not the result of formulas).

Please Login or Register  to view this content.

To process cells in a range:

Please Login or Register  to view this content.

Lewis

I'm having trouble getting the code to work properly - can you advise where to paste the "FindAllNonBlankCells" within my existing code?

To confirm, I have around 60 workbooks in the designated folder that need to be compiled - they all have the same range but there will be blank cells in all of them which need to be missed.

I will keep trying myself in the mean time!

The code you have is very fast and efficient, and I probably gave the right answer to the wrong question.

Is there an easy way to ignore the blank cells?

I assume you mean there are blank cells in the source documents. If there are one or a few blank cells in a row, I don't know how to answer your question. If all the cells in a row are blank, then the row can be deleted after you do all your copying. Is that what you mean?

I apologize for the confusion.

Lewis

Yes, the range I need to copy - B18:N100 - will have blank ROWS only. I would like to skip all the blank rows. If it helps, the rows with data in always appear first, followed by the empty rows underneath. Oh, and one more thing to complicate the matter - I have tried using find last row but there are more tables in the document underneath the rows so we need to tell the VBA to find the last row with data in the range.

Thanks!

Hi Michael,

Ignore the code in my previous post. It is perfectly good code, but not applicable to your current situation. To paraphrase the 'Quiet Man', "Don't use that code, it will do you no good!"

I think the following may solve your problem. Changes from your original are in red. I tested the code as best I could without opening any files.

Please Login or Register  to view this content.

Please let me know if you need further assistance.

Lewis

Thanks Lewis, I think we are nearly there!

However, I am getting a runtime "424 Object Required" error at line "Set SourceRange2 = SourceRange.CurrentRegion"

One thing I noticed is that you said to assume that there is an empty row in the source sheet at row 17 - this however is not the case, it is actually text headers for the columns to be copied, i.e. date, amount, receipt number etc. I don't know if this may be causing the problem....

Hi Michael,

I am getting a runtime "424 Object Required" error at line "Set SourceRange2 = SourceRange.CurrentRegion"

I tested the entire macro, and was unable to duplicate your problem using Excel 2003 and Excel 2010. I was able to generate a Runtime error 1004 (Application Defined or Object Defined Error) on the same line. I corrected the error that caused the problem (in red below) and the error went away.

One thing I noticed is that you said to assume that there is an empty row in the source sheet at row 17 - this however is not the case, it is actually text headers for the columns to be copied, i.e. date, amount, receipt number etc. I don't know if this may be causing the problem....

My previous comment was a typo from an early version of the code. Rows 1 thru 17 can contain anything. See the corrected text in blue below.

Please Login or Register  to view this content.

Please let me know if you have any more problems.

Lewis

I get a "runtime error 424 object required" and also "runtime error 1004 - you cannot use this command on a protected sheet" both on the line "Set SourceRange2 = SourceRange.CurrentRegion"

they happen intermittently, seemingly randomly! very frustrating!

I am on 2007 by the way, don't know if this makes a difference?

So close!-

I was able to duplicate the 1004 error, which was caused by the Sheet being protected. That I can fix.

I was still unable to duplicate the 424 error.

Add the line in red below to the code to attempt to eliminate the 1004 error. If you have a password for the Sheets, put in the password between the quotes. If there is a password, it has to be the same for all workbooks, in the current state of the code.

Please Login or Register  to view this content.

If the 424 error persists, the only way I can help is if you upload two sample workbooks. The one that contains the code, and the one that causes the 424 error.

Remember to desensitize the data and remove unnecessary items if possible.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Lewis

I will have to wait until Monday to get the password from a colleague, but this looks hopeful!

Thanks so much for your help, will let you know how it turns out

So, I have the password, have added into the updated code.... but still no joy!

I am still getting all the blank rows beneath the imported data, any other ideas?

Also, when I have time I will upload the template document with sensitive data removed so you can get a better idea of what I need to achieve.