+ Reply to Thread
Results 1 to 10 of 10

Batch process CSV files delete entire row

  1. #1
    Registered User
    Join Date
    06-11-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Batch process CSV files delete entire row

    Hello and thank you for your time.

    I am trying manipulate some code that I found in another post "VBA to Delete Specific Columns 100s Files for .csv "

    I started with the file that was linked to the above linked thread "EF975086 code.xlsm‎ " file and I'm trying to modify it. I'd like to delete the entire row if the text value matches what I have specified. I'm working with multiple csv files (batch) and all of the data is in column A. I've been able to get the macro to run but it does not delete the rows of data that I am trying to remove. Rather, there are no changes made.

    I need to delete the row that contains the following strings.

    Date/Time, Time stamp, Cycles of Channel 1, Channel 1-Displacement, Channel 1-Load, Channel 1-Dyn Load, Channel 1-Accel

    s, s, cycles, mm, N, N, G

    I very much appreciate your time on this matter.

    Here is the edited code:


    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    400

    Re: Batch process CSV files delete entire row

    This line closes the workbook without saving -
    Please Login or Register  to view this content.
    I think if you change false to true it will work.

  3. #3
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    400

    Re: Batch process CSV files delete entire row

    One more thing, you need to declare each variable individually in VBA. This line:
    Please Login or Register  to view this content.
    declares wb as a Workbook, and k2 as long, but the remaining variables will all default to variant. You should change the line to
    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,748

    Re: Batch process CSV files delete entire row

    See if this works.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-11-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Batch process CSV files delete entire row

    Jindon,

    Thank you for your reply. The code that you posted is what I'm looking for (batch processing). I'm just not sure how to make it look for the text that I'm trying to delete. I tried to replace the pattern ("([$()\|\\\[\]{}+*?.-])") with the text that I want to delete but it did not work. When I run the code you posted it deletes the blank rows in the worksheet.


    I need to search the csv files for: "Date/Time, Time stamp, Cycles of Channel 1, Channel 1-Displacement, Channel 1-Load, Channel 1-Dyn Load, Channel 1-Accel" and "s, s, cycles, mm, N, N, G" and delete the rows that contain this text. Thank you very much for any advice you can offer!

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,748

    Re: Batch process CSV files delete entire row

    No need to change the code.
    All you need to do is place the criteria phrase from cell A2 down as the code you posted.

  7. #7
    Registered User
    Join Date
    06-11-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Batch process CSV files delete entire row

    Jindon,

    Thank you again for your help with this. I've never worked with Regex so I'm not able to do much troubleshooting. When I run this code it only deletes the empty rows but does not delete the strings that I am trying to remove. I've attached a small csv file that maybe will help. From this example.csv file I need to delete rows 13, 14, 19, 20. I could have the problem in that when the csv file is opened for editing, it opens it a comma delimited. I've replaced the long string with only "Date/Time" and "s" but that did not work for me either. Thank you for your time.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,181

    Arrow Re: Batch process CSV files delete entire row


    Hi,

    the beginner easy way - without any code - once the source text file opened in Excel is to apply a filter or an advanced filter.
    Once it works manually, you can easily start to automatize just activating the Macro Recorder and redo the same operations

    Or attach a source text file without modifying its structure and at least a workbook with the keys list
    in order to delete rows and with the expected result according to the source text file.
    Last edited by Marc L; 05-13-2020 at 10:30 AM.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,748

    Re: Batch process CSV files delete entire row

    Ahh, OK.
    I misread the question.

    You should have
    A2:
    Date/Time, Time stamp, Cycles of Channel 1, Channel 1-Displacement, Channel 1-Load, Channel 1-Dyn Load, Channel 1-Accel
    A3:
    s, s, cycles, mm, N, N, G
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-11-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Batch process CSV files delete entire row

    Jindon,

    This works!

    Thank you very much for your help with this. I really appreciate it.

+ 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. Replies: 6
    Last Post: 09-16-2019, 11:53 AM
  2. Monitoring utility for a Batch Process
    By ImranBhatti in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-14-2018, 12:49 PM
  3. vba macro to batch process adding worksheets
    By SKooLZ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2015, 05:07 AM
  4. [SOLVED] Batch job. Open a lot of excel files, delete the same values from each, save, then close
    By greendale in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 11-06-2013, 03:31 PM
  5. [SOLVED] Adapt macro to batch process files
    By beng404 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-27-2012, 07:09 PM
  6. Reverse Geocode Batch Process
    By ernestgoh in forum Excel General
    Replies: 0
    Last Post: 01-19-2010, 03:35 PM
  7. Delete an entire folders contents (or just Excel files)
    By DejaVu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2006, 04:29 PM

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