Need help with VBA for multiple functions

I am trying to learn to use VBA, but am struggling with finding the correct syntax (?) for an office project.

We receive data that comes all in one column. There are generally 3 rows of actual information and then a blank line. This pattern repeats. Some datasets may only have a 25 or so "sets". Others can have over 1,000 sets.

Challenge #1: Determine the last populated row (dataset contains blank rows). I can use a MATCH("zzz",A:A) formula to find the last row.


Challenge #2: Transpose each "group", on a new worksheet.


A very nice person on here helped me with a DROP(WRAPROWS) formula which uses the range found in Challenge #1. Ex: A1:A51. That same formula also "drops" the blank row/cell once the data is transposed.


Challenge #3: Format the transposed data so that it becomes easier to read.


Summary:

Transpose all data from column A (with the last row always being a variable) on one sheet over to a 2nd sheet, add column headers and possibly convert the table to a range (or add borders) just to make the information easier to read.

I feel like there is no single formula (or nested formulas) that will do all of that and that we probably need to use VBA to accomplish all of these things, but I am not experienced enough to write all of this.

If anyone has time to help, it would be much appreciated.

Thanks in advance.

Please update your profile. If you have DROP(WRAPROWS()), you are not using Excel 2016.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

If you have Excel 2016, this formula may not work.

Formula:

Please Login or Register  to view this content.

Please try this VBA macro.

Please Login or Register  to view this content.

This:

Formula:

Please Login or Register  to view this content.

Updated to reflect the sample file data:

Formula:

Please Login or Register  to view this content.

Update - Better:

Formula:

Please Login or Register  to view this content.

If you do want to use VBA here's a solution that works for your example file having 5 rows of data instead of the 3 you talk about in your 1st post.

Please Login or Register  to view this content.

?

Please Login or Register  to view this content.

Try this code

Please Login or Register  to view this content.

As we love options...

Please Login or Register  to view this content.

Which macro? You've got five (if I’ve counted correctly).

And more important, which error message are you getting?
Post an example file which reflects the true situation of your data because the file you present in your 1st post doesn't represent your explanation in Post#4.

I assume based on the rep points added...This is solved?

Well Sintek, at least you got rep points. LOL

@bakerman2...Yeah...Don't quite understand that...

Did you not...Yours too solves...