Got my Macro to skip blank rows when creating the CSV, but I need it to remove them

[sbar7]

I've been searching and searching for something that is probably simple but I can't seem to find the answer and I don't know enough about VBA myself to figure it out on my own. This macro was initially created by someone else in our company (no idea who) and I'm trying to craft it to better fit what I need it to do. Below is what I have now. The macro creates a CSV file from a worksheet of formulas pulling data from another worksheet. The formulas return blanks ("") for cells in rows with zeroes or no real data. I need the CSV to contain none of those rows with no data.

I think the only successful addition I've made to the original Macro was to skip rows with no data, though I guess that's not really even that successful since it still returns empty rows on the CSV and it turns out I actually need them deleted altogether.

Public Sub Upload_Converter()
        Const DELIMITER As String = "|"
        Dim LastRow As Long
        Dim myField As String
        Dim nFileNum As Long
        Dim sOut As String
        Dim FilePath As String
      
        Dim dte As String
 
        dte = InputBox("Please Enter Event Date (mm/dd/yyyy Format): ", Default:=Format(Now, "mm/dd/yyyy"))
        FilePath = ActiveWorkbook.Path & "\AR_Entry_" & Format(dte, "yyyymmdd") & "_" & Format(Now(), "hh24mmss") & ".csv"
      
        LastRow = ActiveSheet.UsedRange.Rows.Count
          
        sOut = Empty
      
        nFileNum = FreeFile
        Open FilePath For Output As #nFileNum
      
        For i = 2 To LastRow
            For j = 1 To 7
                If j = 1 Then
                    myField = Cells(i, j).Value
                    ' myField = Right("0000000000" & myField, 10)
                    sOut = sOut & myField
                ' ElseIf j = 3 Then
                    ' myField = Format(Cells(i, j).Value, "mm/dd/yyyy")
                ElseIf j = 7 Then
                    myField = Format(Cells(i, j).Value, "###0.000")
                    If IsEmpty(Cells(i, j).Value) Then
                        myField = Format(0, "###0.000")
                    End If
                    sOut = sOut & DELIMITER & myField
                Else
                    myField = Cells(i, j).Value
                    sOut = sOut & DELIMITER & myField
                End If
             Next j
             Print #nFileNum, Mid(sOut, 1)
             sOut = Empty
           
        Next i
 
        Close #nFileNum
      
        MsgBox ("File is converted - reference file AR_Entry.csv in " & FilePath)
End Sub

Here's a simple example of an output CSV, followed by how I need it to be.

 

[DanteAmor]

Try this:

Public Sub Upload_Converter()
  Const DELIMITER As String = "|"
  Dim i As Long, j As Long, LastRow As Long, nFileNum As Long
  Dim myField As String, sOut As String, FilePath As String, dte As String
  
  dte = InputBox("Please Enter Event Date (mm/dd/yyyy Format): ", Default:=Format(Now, "mm/dd/yyyy"))
  FilePath = ActiveWorkbook.Path & "\AR_Entry_" & Format(dte, "yyyymmdd") & "_" & Format(Now(), "hh24mmss") & ".csv"
  
  LastRow = ActiveSheet.UsedRange.Rows.Count
  
  sOut = Empty
  
  nFileNum = FreeFile
  Open FilePath For Output As #nFileNum
  
  For i = 2 To LastRow
    If WorksheetFunction.CountA(Range("A" & i).Resize(1, 7)) > 0 Then
      For j = 1 To 7
        If j = 1 Then
          myField = Cells(i, j).Value
          sOut = sOut & myField
        ElseIf j = 7 Then
          myField = Format(Cells(i, j).Value, "###0.000")
          If IsEmpty(Cells(i, j).Value) Then
            myField = Format(0, "###0.000")
          End If
          sOut = sOut & DELIMITER & myField
        Else
          myField = Cells(i, j).Value
          sOut = sOut & DELIMITER & myField
        End If
      Next j
      Print #nFileNum, Mid(sOut, 1)
      sOut = Empty
    End If
  Next i
  
  Close #nFileNum
  
  MsgBox ("File is converted - reference file AR_Entry.csv in " & FilePath)
End Sub

 

[sbar7]

Try this:

Public Sub Upload_Converter()
  Const DELIMITER As String = "|"
  Dim i As Long, j As Long, LastRow As Long, nFileNum As Long
  Dim myField As String, sOut As String, FilePath As String, dte As String
 
  dte = InputBox("Please Enter Event Date (mm/dd/yyyy Format): ", Default:=Format(Now, "mm/dd/yyyy"))
  FilePath = ActiveWorkbook.Path & "\AR_Entry_" & Format(dte, "yyyymmdd") & "_" & Format(Now(), "hh24mmss") & ".csv"
 
  LastRow = ActiveSheet.UsedRange.Rows.Count
 
  sOut = Empty
 
  nFileNum = FreeFile
  Open FilePath For Output As #nFileNum
 
  For i = 2 To LastRow
    If WorksheetFunction.CountA(Range("A" & i).Resize(1, 7)) > 0 Then
      For j = 1 To 7
        If j = 1 Then
          myField = Cells(i, j).Value
          sOut = sOut & myField
        ElseIf j = 7 Then
          myField = Format(Cells(i, j).Value, "###0.000")
          If IsEmpty(Cells(i, j).Value) Then
            myField = Format(0, "###0.000")
          End If
          sOut = sOut & DELIMITER & myField
        Else
          myField = Cells(i, j).Value
          sOut = sOut & DELIMITER & myField
        End If
      Next j
      Print #nFileNum, Mid(sOut, 1)
      sOut = Empty
    End If
  Next i
 
  Close #nFileNum
 
  MsgBox ("File is converted - reference file AR_Entry.csv in " & FilePath)
End Sub

No luck :/

The lines with data are wrong now too. Looks like you have it as only 7 columns instead of 9 and it's treating column 7 (dates) as a number like it should for column 9.

 

[DanteAmor]

For j = 1 To 7

That line covers only 7 columns, I didn't modify that part.

No luck :/

You have formulas or blank spaces in cells. In my tests if there are 7 empty columns then it doesn't create the row.
Could you share your file for me to review.

You could upload a copy of your file to a free site such google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[sbar7]

That line covers only 7 columns, I didn't modify that part.


You have formulas or blank spaces in cells. In my tests if there are 7 empty columns then it doesn't create the row.
Could you share your file for me to review.

You could upload a copy of your file to a free site such google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Wow, I screwed something up apparently and pasted the wrong version. I don't even remember changing it to that. Sorry for wasting your time there. I won't be back to office until Monday to update. I'll let you know.

 

[sbar7]

First off, I discovered that what I pasted was a different upload export macro unrelated to this particular export I need help with.

Second and most importantly, I was able to basically take what you did and apply it to the macro and it works! Here is the actual vba (*** lines were not originally there, taken from your version):

Public Sub Upload_Converter()
        Const DELIMITER As String = "|"
        Dim LastRow As Long
        Dim myField As String
        Dim nFileNum As Long
        Dim sOut As String
        Dim FilePath As String
  
        Dim dte As String
 
        dte = InputBox("Please Enter Event Date (mm/dd/yyyy Format): ", Default:=Format(Now, "mm/dd/yyyy"))
        FilePath = ActiveWorkbook.Path & "\AR_Entry_" & Format(dte, "yyyymmdd") & "_" & Format(Now(), "hh24mmss") & ".csv"
  
        LastRow = ActiveSheet.UsedRange.Rows.Count
      
        sOut = Empty
  
        nFileNum = FreeFile
        Open FilePath For Output As #nFileNum
  
***     Dim i As Long, j As Long
  
        For i = 2 To LastRow
***         If WorksheetFunction.CountIf(Range("A" & i).Resize(1, 9), "") < 5 Then
                For j = 1 To 9
                    If Cells(i, 1).Value <> "" Then       - 'this is what I had added, but isn't needed
                        If j = 1 Then
                            myField = Cells(i, j).Value
                            sOut = sOut & myField
                        ElseIf j = 7 Then
                            myField = Format(Cells(i, j).Value, "mm/dd/yyyy")
                            sOut = sOut & DELIMITER & myField
                        ElseIf j = 9 Then
                            myField = Format(CSng(Cells(i, j).Value), "###0.000")
                            sOut = sOut & DELIMITER & myField
                        Else
                            myField = Cells(i, j).Value
                            sOut = sOut & DELIMITER & myField
                        End If
                    End If
                Next j
                Print #nFileNum, Mid(sOut, 1)
                sOut = Empty
***         End If
        Next i
                 
        Close #nFileNum

        MsgBox ("File is converted - reference file AR_Entry.csv in " & FilePath)
End Sub

The only tweak I made to your solution was to change from CountA to CountIf because the "blank" cells were really formulas that returned "".

Thank you, Mr. Amor!