Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Delete Blank Rows

    I have several cvs files in a folder called JNLS

    I would like a macro to open the CVS files in folder C:\JNLS and to delete all the blank rows in Col A and then to save these files

    Your assistance in this regard is most appreciated

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does this do what you want?

    Code:
    Option Explicit
    Sub OPenCSVDeleteBlanks()
      Dim strPath As String
      Dim strFile As String
      Dim wbk As Workbook
      Dim lRow As Long
      strPath = "C:\JNLS\"
      strFile = Dir(strPath & "*.csv")
      Do While strFile <> ""
        Set wbk = Workbooks.Open _
          (Filename:=strPath & "\" & strFile, _
            UpdateLinks:=0, _
            ReadOnly:=True, _
            AddToMRU:=False)
        wbk.Worksheets(1).Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        strFile = strFile = Left(strFile, Len(strFile) - 3) & "xlsx"
        wbk.SaveAs _
          Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
        wbk.Close (False)
        strFile = Dir
      Loop
    End Sub
    [Note: Change the appropriate lines to:
    strFile = strFile = Left(strFile, Len(strFile) - 3) & "xls"
    wbk.SaveAs strPath & strFile, FileFormat:=xlExcel8

    If you want to saveAS a XL93-2003 file]

    Steve
    Last edited by sdckapr; 2013-09-25 at 12:31. Reason: 1) Simplified code by using specialcells, rather than autofilter. 2)Replaced substitute with left...

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help. Once the blank rows are deleted, I would like to save as CSV file. Please amend code accordingly

    Tried to change your code, but comes up with message "cannot save as that name-document was opened as read only'

    HTML Code:
    Option Explicit
    Sub OPenCSVDeleteBlanks()
    Application.DisplayAlerts = False
    
      Dim strPath As String
      Dim strFile As String
      Dim wbk As Workbook
      Dim lRow As Long
      strPath = "C:\JNLS\"
      strFile = Dir(strPath & "*.csv")
      Do While strFile <> ""
        Set wbk = Workbooks.Open _
          (Filename:=strPath & "\" & strFile, _
            UpdateLinks:=0, _
            ReadOnly:=True, _
            AddToMRU:=False)
        wbk.Worksheets(1).Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
          wbk.SaveAs _
          Filename:=strPath & strFile, FileFormat:=xlCSV
          wbk.Close (False)
        
        strFile = Dir
      Loop
       
    End Sub



    Regards

    Howard

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What happens if you don't open as readOnly (change the line in the open to be):

    ReadOnly:=False, _

    [I opened as read only since I thought you were saving as XL, not resaving the CSV]...

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks-works perfectly now

    Howard

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    you can probably get rid of the SAVEAS line all together and just use:
    wbk.Close (True)

    To just close the wbk and save it since you aren't changing the format of the file.

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the tip

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •