Excel Macro to delete rows and then transpose data

I am reaching out to request your expertise in creating an Excel macro to streamline a task I frequently encounter. Specifically, I need to delete all rows that are highlighted in yellow and then transpose the data to their respective fields. (Blue area)

I have a spreadsheet with data, and certain rows are highlighted in yellow to indicate that they need to be deleted (" Manage Columns" and 2nd "apn". Manually deleting these rows and then transposing the data is becoming time-consuming and error-prone, so I believe automating this process with a macro would greatly improve efficiency.

After data has been cleaned we then need to transpose data as you see the data in blue.

Very not logical : as once deleted, how can you 'transpose' data ?‼

According to the obvious missing explanation (which process ?!)
share at least a before worksheet and accordingly the exact expected result
in order to see if only some logic can be applied. No logic, no automatization …

Hi there,

The data columns required do not correspond either in number or in sequence with the data rows which seem to be associated with a typical property.

More information is required.

Regards,

Greg M

Correct, the sequence of data is not consistent but the data that is in yellow is junk.

The first process is to delete junk data and then transpose it. but then i will need to fix it if it has less than full data.

I apologize but I colored the data to make it easier on you so you can understand what's going on. The blue section is the final product.

This is my 3 rd time replying back but the server is not showing my replies.

Okay, it seems to be working now. Can you please rewrite the code without using any colors as your parameters.? I would recommend instructing Excel to delete all rows between "Manage columns" and "apn" and then "apn" to "apn". I tried using Chat GPT but it doesn't get what I want. BTW the code must reflect all instances not just the first instance it sees. The data in yellow is all junk. The original file contains no colors so let's use keywords as I have suggested.

I don't understand how you want to "transpose" though...

Please Login or Register  to view this content.

Thank you for helping out but this process should be done in stages.

Stage1: clear junk data
Stage 2: Transpose to headers in blue

No idea about transpose, so just 1)

Please Login or Register  to view this content.

Thanks for trying but it doesn't clean the whole file.

What do you mean?

You said delete yellow cells, and the code should do it.

If you don't want my help, just say so.

I do need help but I encourage you to read the whole thread.

Forget transposing it. Lets just clean it up.

RECAP: Can you please rewrite the code without using any colors as your parameters.? I would recommend instructing Excel to delete all rows between "Manage columns" and "apn" and then "apn" to "apn". I tried using Chat GPT but it doesn't get what I want. BTW the code must reflect all instances not just the first instance it sees. The data in yellow is all junk. The original file contains no colors so let's use keywords as I have suggested.

Have you really tried the code?

My code deletes the cells regardless of the cell color.

Can I send you the full file and you try it for yourself? There are about 30k cells

Alan,

The logic for deletion is simple and clear, but "transpose" part...

Good job on cleaning the file. It works and it's so fast. Thank you.

I cannot figure out what needs to be done to delete Manage Columns.

Please Login or Register  to view this content.

Perfect. Thank you for helping out. How can I learn VBA? Is there a course you would recommend?

Completely self-taught in my case, so not following common rule of naming convention of the variable etc.

You can always refer to the page

New to Excel Programming and looking for learning materials

What about "transpose" part?

Thank you so much for helping out.

Here is a video of what is required. Couldn't describe it with words.
https://youtu.be/k5M-NCN-Ztc

Thanks again

OK,

If you upload a workbook showing before/after will help.
Not too many rows, just about enough rows of transposed rows with source data, so that I may find the logic.

Data should by dummy, but data types must be identical to your actual data.

Okay here is the file

Thanks for the code but I'm getting a security error message. It is an XLSm file but Excel is not letting the code run due to security. Any idea how to turn off security? I don't use Macros at all and am not very well-versed in this area.

I changed the settings to run vba but now getting this error messageScreenshot 2023-10-07 140437.png

Just delete line in red at the bottom.

@ JinDon Thank you so much. I'm amazed at how well you know Excel.

Last question - Do you know how to label this thread as Solved now?

Perhaps "Thread Tools" above your 1st post? (never done it for myself...)