+ Reply to Thread
Results 1 to 12 of 12

Ignore blanks in Compile workbooks code

  1. #1
    Registered User
    Join Date
    04-08-2015
    Location
    Chamonix
    MS-Off Ver
    2007
    Posts
    9

    Ignore blanks in Compile workbooks code

    Hello all, I have been working on a code allowing me to compile many workbooks via a VBA macro, trouble is that in the set range there are many blank cells.

    Is there an easy way to ignore the blank cells?

    Code:

    Please Login or Register  to view this content.


    Many thanks
    Last edited by MichaelHopkins; 04-10-2015 at 04:00 AM.

  2. #2
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: 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

  3. #3
    Registered User
    Join Date
    04-08-2015
    Location
    Chamonix
    MS-Off Ver
    2007
    Posts
    9

    Re: Ignore blanks in Compile workbooks code

    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!

  4. #4
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Ignore blanks in Compile workbooks code

    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

  5. #5
    Registered User
    Join Date
    04-08-2015
    Location
    Chamonix
    MS-Off Ver
    2007
    Posts
    9

    Re: Ignore blanks in Compile workbooks code

    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!

  6. #6
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Ignore blanks in Compile workbooks code

    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

  7. #7
    Registered User
    Join Date
    04-08-2015
    Location
    Chamonix
    MS-Off Ver
    2007
    Posts
    9

    Re: Ignore blanks in Compile workbooks code

    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....

  8. #8
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Ignore blanks in Compile workbooks code

    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

  9. #9
    Registered User
    Join Date
    04-08-2015
    Location
    Chamonix
    MS-Off Ver
    2007
    Posts
    9

    Re: Ignore blanks in Compile workbooks code

    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!-

  10. #10
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Ignore blanks in Compile workbooks code

    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

  11. #11
    Registered User
    Join Date
    04-08-2015
    Location
    Chamonix
    MS-Off Ver
    2007
    Posts
    9

    Re: Ignore blanks in Compile workbooks code

    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

  12. #12
    Registered User
    Join Date
    04-08-2015
    Location
    Chamonix
    MS-Off Ver
    2007
    Posts
    9

    Re: Ignore blanks in Compile workbooks code

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code Needed - Move Data/Ignore Blanks/Data is Moved to Specific Area
    By kbarnes04 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-22-2014, 03:30 PM
  2. [SOLVED] MIN/MAX IF To Ignore Blanks
    By splendidus in forum Excel General
    Replies: 2
    Last Post: 07-04-2012, 04:00 PM
  3. Compile Errors - Is it possible to ignore them?
    By Shane O in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-13-2011, 09:45 AM
  4. VB code help, ignore blanks/specific
    By ad9051 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-08-2011, 04:38 PM
  5. Concatenate and ignore blanks
    By nujwaan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-22-2009, 04:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1