Delete Empty Rows of Table And Optimize Code

I have four worksheets (see sample). The goal is to copy Col A from the first three worksheets to Col A of the fourth worksheet, sort the result and get rid of empty rows. Here is what I have done so far in the code below:

Delete old data from Col A of table on Worksheet 4
Copy Col A of Worksheet 1 to Col A of Worksheet 4
Copy Col A of Worksheet 2 to Col A of Worksheet 4
Copy Col A of Worksheet 3 to Col A of Worksheet 4
Sort Col A of Worksheet 4 to separate data from empty cells

Please Login or Register  to view this content.

My original idea was to copy the data into an array or variable, sort the data to exclude the empty cells and then paste the data into Worksheet 4 but I am sorely lacking in the proper skills.

Would you kindly look this over and suggest a better way?

Thanks!

Hi GC Excel, berlan and protonLeah,

A hardy THANKS to all three of you for your thoughtful help. I am learning so much I'm gonna be dangerous one day! I have loaded, studied and tried all three solutions. All three do the job for the most part. I will now work with some elements of all three and post my solution for the benefit of all members. In the meantime here is what I found:

GC Excel's post expanded nicely on my effort by doing away with the individual variables and using For-Next to cycle through. The use of an extra worksheet works fine. This procedure needs the addition of code to erase sheet Four before pasting. Easy to fix.

berlan also shortened my procedure nicely, but went way beyond my meager approach by doing the entire copy/paste in just a few lines of code. Very impressive...but then, I am easily impressed. ;-) This code worked perfectly.

protonLeah came up with the solution I think I was looking for by using an array. This code will take a lot more study on my part to understand what is happening but I am guessing that if we clock it, it might be the fastest, which is great because the real workbook has thousands of rows in each sheet. This solution deleted the entire results table along with its formulas but that even I can fix.

My comments may seem like the teacher lecturing students...nothing could be farther from the truth. I have learned so much from the three of you that I will be documenting my discoveries all day!

As I said, I will now attempt to cobble together all the great work into one solution and post it here for all.

Thanks again for the generous gift of your time and energy. I really appreciate it.

Thanks for the feedback, very much appreciated

Hi protonLeah,

I attempted to modify your code to delete only the rows of Sheet Four from Row 3 down but my "fix" caused an error with the last line of code, which I also modified. I fear I have made matters worse. I also need to sort the result.

Help, please! And thank you.

Please Login or Register  to view this content.