+ Reply to Thread
Results 1 to 4 of 4

Macro that can be applied to any workbook that needs same editing

  1. #1
    Registered User
    Join Date
    01-31-2017
    Location
    Philippines
    MS-Off Ver
    15
    Posts
    18

    Red face Macro that can be applied to any workbook that needs same editing


    Hi experts, I'm really stressed on how can i easily delete all unnecessary
    columns & rows, and then combining the text in J , U and V column.

    I want file #1..
    to become file #2.

    I already tried macro recording and saving the macro in my personal workbook but
    when i run the same macro on other work book, its not working. Honestly, my knowledge in using
    vba is very little so i really need your help.
    Attached Files Attached Files
    Last edited by khr0n0s; 03-09-2018 at 02:54 AM. Reason: requested by admin

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,706

    Re: Please, I need macro to utilize this file for my work.

    Hi khr0n0s,

    Using Power Query the new tool built into Excel, your problem took me about 2 minutes. PQ is called Get & Transform in 2016 Excel.

    Here is the Query

    Please Login or Register  to view this content.
    But I didn't need to write any of the code above. Open the attached and then look at the Power Query and edit it. It is much easier than VBA code to do this stuff.

    Power Query CSV1 to 2.xlsx

    If you put the 1.csv on your desktop and change the source of the above code you can repro what I did. OR - Learn a bit more about Power Query.
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Registered User
    Join Date
    01-31-2017
    Location
    Philippines
    MS-Off Ver
    15
    Posts
    18

    Re: Please, I need macro to utilize this file for my work.

    Quote Originally Posted by MarvinP View Post
    Hi khr0n0s,

    Using Power Query the new tool built into Excel, your problem took me about 2 minutes. PQ is called Get & Transform in 2016 Excel.

    Here is the Query

    Please Login or Register  to view this content.
    But I didn't need to write any of the code above. Open the attached and then look at the Power Query and edit it. It is much easier than VBA code to do this stuff.

    Attachment 564783

    If you put the 1.csv on your desktop and change the source of the above code you can repro what I did. OR - Learn a bit more about Power Query.
    I'm a little lost..so can i load and apply the query in different workbook with different data so that i won't go through manually edit every workbook again?

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,706

    Re: Macro that can be applied to any workbook that needs same editing

    If your CSV file always looks like what you gave, then yes.

    You also need either Excel 2016 which has the "Get & Transform" tool or download it and use it as an Add-in called Power Query if you have Excel 2010 or 2013. I also believe you need to be using the PC (not Mac) version of Excel.

    Search on Power Query and see some examples of PQ and how to use it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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