+ Reply to Thread
Results 1 to 5 of 5

Need some help please - Macro to create a CSV file and remove blank rows.

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    3

    Need some help please - Macro to create a CSV file and remove blank rows.

    Good Morning to you all.

    I have the existing VBA code (see below) to create a CSV file from a excel sheet and this work well, however the problem is the software that reads the CSV file does not like data missing in rows or enire blank rows.

    Therefore I need a macro that would amend the new CSV file as it is been created to remove entire rows when data is not present in column A.

    Is this possible?

    Sub CopyToCSV()
    Dim MyPath As String
    Dim MyFileName As String
    'The path and file names:
    MyPath = "C:\V10\demo\import\"
    MyFileName = Sheets(1).Cells(1, 1).Value
    'Makes sure the path name ends with "\":
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
    'Makes sure the filename ends with ".csv"
    If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
    'Copies the sheet to a new workbook:
    Sheets("upload").Copy
    'The new workbook becomes Activeworkbook:
    With ActiveWorkbook
    'Saves the new workbook to given folder / filename:
    .SaveAs Filename:= _
    MyPath & MyFileName, _
    FileFormat:=xlCSV, _
    CreateBackup:=False
    'Closes the file
    .Close False
    End With
    End Sub

    Thanks in anticipation.

    Stuart

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Need some help please - Macro to create a CSV file and remove blank rows.

    How aboth setting an autofilter on the excel file and setting the "Field" as 1 and "Criteria1 as "<>" to get rid of the blank rows, something like

    Please Login or Register  to view this content.
    and the copy the filterd rang using the command

    Please Login or Register  to view this content.
    and the make a CSV file of the copied range.

    Alf

  3. #3
    Registered User
    Join Date
    09-25-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need some help please - Macro to create a CSV file and remove blank rows.

    Thanks for the reply but this doesn't seem to work.

    Attached is the spreadhseet I have so far with a marco that isn't now working, any help would be appreciated?

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Need some help please - Macro to create a CSV file and remove blank rows.

    You could try this macro instead as you have both blanks and zero values in column A

    Please Login or Register  to view this content.
    I set the autofilter range based on last value in column A but found later out that there were more rows i column i and j with value i.e. "#Ref!" so you either use column i or j for setting the autofilte range or you delete those two last rows so that A column has the same number of rows as i and j column.

    I've inactivated copy part with the ' since I think you should copy and paste this to a sheet in you file so you can have a look at the result first to see if you can convert it to a csv file that you software can read without any problems.

    Alf

  5. #5
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Need some help please - Macro to create a CSV file and remove blank rows.

    Here's the long version.
    Please Login or Register  to view this content.
    David
    (*) Reputation points appreciated.

+ 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] How to use a macro to remove rows whose values in column A equal to blank spaces?
    By billj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-16-2014, 06:38 AM
  2. How to create a macro to insert blank rows and copy data into blank rows?
    By zodiack101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 01:18 PM
  3. Macro to remove extra blank rows
    By alexduy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2013, 01:20 PM
  4. [Macro] Remove blank rows (apart from 1 to separate datasets)
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-08-2011, 05:41 PM
  5. Macro to remove the blank rows and detect header
    By coolanks1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-20-2009, 03:40 AM
  6. Need macro to remove blank rows
    By Bob in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-21-2006, 08:25 AM
  7. [SOLVED] Can I create a macro to identify and delete blank rows in a range?
    By carlsondj in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-09-2005, 08:05 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