[VBA] Script is ignoring worksheets with blank cells

  • Hi all,


    I have some issue with the following code :


    My worksheets tend to have some blank cells in column A and B, so the current script only pulls data where there are non-empty cells in column A, is there a workaround here as I would want to include the rows that has empty cells in column A as well in my "Combined" Sheet. Please refer to attached for a picture reference. Thanks for reading!


  • You can't use CurrentRegion if the data has empty Rows and/or empty columns. Try this


  • You can't use CurrentRegion if the data has empty Rows and/or empty columns. Try this


    Hi Roy, thank you very much for your assistance, I tried inputting your code, however it is running into error :

    Run Time error 9 "Subscript out of range"


    Any idea what would be causing this?


    My full code if it helps

  • scratch this qn, as I forgot to add the worksheet "Combined" hence the error.

  • You can't use CurrentRegion if the data has empty Rows and/or empty columns. Try this


    However Roy, even with your amendments, the script fails to combine all the data onto one. I've attached a file for benchmarking, hope you find it useful as I cannot resolve the issue, and I do appreciate your help greatly!


    If everything is working, there should be 326 rows of data.


    Thanks in advance!