+ Reply to Thread
Results 1 to 8 of 8

.csv Import. Resize table to match data set

  1. #1
    Registered User
    Join Date
    11-12-2018
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2016 (Office 365 ProPlus)
    Posts
    58

    Lightbulb .csv Import. Resize table to match data set

    I have written a macro module to aid users import .csv files into some empty tables ready for further analysis. These .csv files always contain three columns of data, but the amount of rows varies greatly and can be in the thousands. Previously we used to copy and paste values, but this is notoriously slow. Instead we have switched to using ''Range.Value ='' (shown in green below).

    This has sped things up massively, but I am struggling to size the receiving table to match the amount of rows from the incoming data set. As a work around I have just manually set, using a resize function, to a value that I know to be larger than the amount of rows in the data set (shown in blue below and set in this example to 100 rows). This will then just show any empty rows as #N/A. Not pretty!

    I could write some code to remove the #N/A rows, but I am sure there must be a neater way to do this from the off. Can anyone let me know how to code this so each table will match the size of their imported data set? I have attached a striped down version of the macro file along with a test .csv file to allow you to try this out. Thanks in advance for any help.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mattydboom1; 09-11-2020 at 06:35 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    532

    Re: .csv Import. Resize table to match data set

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-12-2018
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2016 (Office 365 ProPlus)
    Posts
    58

    Re: .csv Import. Resize table to match data set

    This works perfectly, thank you ikboy! Its always a thing of beauty to see my janky coding simplified to something so elegant.

    For the purposes of bettering my knowledge would you mind just putting some wording to the logic of the below highlighted sections. I follow everything else but this bit is new to me. In the future I may need to change the columns being imported (for example columns D & H, or maybe even import a third column) so would be good to know how to modify this correctly to suit.

    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    532

    Re: .csv Import. Resize table to match data set

    Please Login or Register  to view this content.
    HTH
    ikboy

  5. #5
    Registered User
    Join Date
    11-12-2018
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2016 (Office 365 ProPlus)
    Posts
    58
    That’s perfect thank you. Figured out how to chop and change columns and bring across additional ones too

    Only thing I can’t figure out is how you would modify to ignore the first row of the .csv data set. Sometimes this contains heading to the columns of data so I would need a way to only look from cell A2 onwards. Is this possible? Suspect I am missing something easy!

  6. #6
    Registered User
    Join Date
    11-12-2018
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2016 (Office 365 ProPlus)
    Posts
    58

    Re: .csv Import. Resize table to match data set

    I thought it may be a case of changing the below line from (1,1) to (2,1) but when I run the macro it always imports the first row as well.

    Please Login or Register  to view this content.
    I see now that the 'CurrentRegion' function will grab values from all cells that aren't blank, so this is why it always include the first row even when stating (2,1), as in my case they will include titles for the columns. So I guess what is needed is another line of code with an 'Offset' function perhaps. Or maybe it could be integrated into this same line somehow? I have googled around an cant find the right method...
    Last edited by mattydboom1; 09-13-2020 at 05:32 AM.

  7. #7
    Registered User
    Join Date
    11-12-2018
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2016 (Office 365 ProPlus)
    Posts
    58

    Re: .csv Import. Resize table to match data set

    Think I may have got something that works to ignore the header row. But if possible to do more simply please let me know! Thanks

    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    532

    Re: .csv Import. Resize table to match data set

    Please Login or Register  to view this content.
    HTH
    Ikboy

+ 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] Auto Resize Table upon data entry from separate sheet
    By sorensjp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-01-2020, 06:50 AM
  2. [SOLVED] Import Excel Data to Find Match in Access Table
    By CRW in forum Access Tables & Databases
    Replies: 3
    Last Post: 09-15-2012, 08:11 AM
  3. Macro to import and resize images to cell
    By wilsonyeoh in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2011, 01:58 AM
  4. Resize range after import
    By gnoke in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-30-2010, 07:49 AM
  5. Excel Macro Import web image, crop, and resize.
    By jdax57 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2009, 08:30 AM
  6. Import Pics and Resize at the same time
    By silver_wire in forum Excel General
    Replies: 0
    Last Post: 08-25-2006, 05:04 PM
  7. Import And Resize Images
    By 2eXtreme in forum Excel General
    Replies: 6
    Last Post: 02-05-2006, 12:00 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