+ Reply to Thread
Results 1 to 33 of 33

Excel Macro to delete rows and then transpose data

  1. #1
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

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

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Excel Macro to delete rows and then transpose data


    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 …

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,603

    Re: Excel Macro to delete rows and then transpose data

    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

  4. #4
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

    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.

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,603

    Re: Excel Macro to delete rows and then transpose data

    Hi again,

    The following code will delete the rows which have been highlighted in yellow:

    Please Login or Register  to view this content.


    . . . and then transpose it.

    I assume that you mean transpose the remaining data, but (as your data rows contain no header information) that can't be done if there is no obvious correlation between (possibly missing?) data rows and the required data columns.


    Hope this helps.

    Regards,

    Greg M
    Last edited by Greg M; 10-01-2023 at 08:29 PM. Reason: Comment re transposition added.

  6. #6
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose 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.

  7. #7
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

    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.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    I don't understand how you want to "transpose" though...
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

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

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

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    No idea about transpose, so just 1)
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

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

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    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.

  13. #13
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

    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.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    Have you really tried the code?

    My code deletes the cells regardless of the cell color.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

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

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    OK, the current code assumes 10000 is the max row, so
    if you change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    should work till end of the data in col.A.
    and if you also want to delete "Manage Columns",
    remove +1 from 2 places
    Please Login or Register  to view this content.
    Last edited by jindon; 10-03-2023 at 11:47 AM.

  17. #17
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 VersionVersion 2411 Win 11
    Posts
    24,409

    Re: Excel Macro to delete rows and then transpose data

    I think the issue at hand is there is no specific criteria for deleting the particular rows. You know which rows to delete and have manually identified them. What criteria should be used that would let Excel know which rows to delete. What are the characteristics of the rows to be deleted?
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    Alan,

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

  19. #19
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 VersionVersion 2411 Win 11
    Posts
    24,409

    Re: Excel Macro to delete rows and then transpose data

    @jindon
    Ok. I guess this one is beyond my comprehension. Good Luck with this OP.
    Alan

  20. #20
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

    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.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

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

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    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?

  24. #24
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

    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

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    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.

  26. #26
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

    Okay here is the file
    Attached Files Attached Files

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    See if this works.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jindon; 10-06-2023 at 06:58 PM.

  28. #28
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

    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.

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    If you can not run any vba code at all in your environment, you need to talk to IT person.

    Or just open the workbook that you have in your local PC, and paste the code I uploaded and run.

    Or you mean?
    Change macro security settings in Excel
    Last edited by jindon; 10-07-2023 at 06:03 AM.

  30. #30
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

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

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

    Just delete line in red at the bottom.

  32. #32
    Registered User
    Join Date
    10-01-2023
    Location
    USA
    MS-Off Ver
    Latest
    Posts
    15

    Re: Excel Macro to delete rows and then transpose data

    @ 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?

  33. #33
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Excel Macro to delete rows and then transpose data

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

+ 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. [SOLVED] Macro to Transpose 5 Rows of Data to a New Sheet as per sample
    By sameer79 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-21-2022, 04:05 AM
  2. I need a Macro script to copy a column, delete the additional rows and paste as transpose
    By mastersaanvi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2018, 02:05 AM
  3. Copy, transpose paste, and delete rows macro
    By johnjoepaddyfrancie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2017, 08:58 AM
  4. [SOLVED] Macro to transpose data from two columns into multiple rows
    By Wolopter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-23-2014, 07:18 AM
  5. [SOLVED] Macro to Transpose data in rows to columns
    By Shanthuday in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2012, 08:00 AM
  6. Replies: 1
    Last Post: 07-13-2012, 08:17 AM
  7. delete data rows in excel by using a macro?
    By Marcia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2006, 05:35 PM

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