Ignoring Blank Cells in CSV Export of Excel Worksheet Data

Question

I am trying to determine a method for 'ignoring' blank cells on a CSV file that is resulting from an Excel worksheet's data.

The blank cell will be a result from user's input in one row OR the row below (both rows will never be filled in however, the header being referenced in a script needs to remain the same). One row pertains to one OS's disk requirements while the row below it pertains to an alternative OS's disk requirements, if applicable; the format of the disk requirements will still apply to the header in its current format.

Currently when the VBA code is applied to the Excel worksheet, the missing data from the blank row pertaining to the first OS disk requirements 'pushes' the next row's information by inputting a blank cell. I would like to find a way to have the VBA code 'ignore' the blank row and have the proceeding row's data be input into this cell instead. The VBA code I am using:

Sub WriteCSVFile()

Dim My_filenumber As Integer
Dim logSTR As String

My_filenumber = FreeFile

logSTR = logSTR & "Header1" & " , "
logSTR = logSTR & "Header2" & " , "
logSTR = logSTR & "Header3" & " , "
logSTR = logSTR & "Header4" & " , "
logSTR = logSTR & "Header5" & " , "
logSTR = logSTR & "Header6" & " , "
logSTR = logSTR & "Header7" & " , "
logSTR = logSTR & "Header8" & " , "
logSTR = logSTR & "Header9" & " , "
logSTR = logSTR & "Header10" & " , "
logSTR = logSTR & "Header11" & " , "
logSTR = logSTR & "Header12" & " , "
logSTR = logSTR & "Header13" & " , "
logSTR = logSTR & Chr(13)
logSTR = logSTR & Cells(18, "C").Value & " , "
logSTR = logSTR & Cells(19, "C").Value & " , "
logSTR = logSTR & Cells(20, "C").Value & " , "
logSTR = logSTR & Cells(21, "C").Value & " , "
logSTR = logSTR & Cells(22, "C").Value & " , "
logSTR = logSTR & Cells(26, "C").Value & " , "
logSTR = logSTR & Cells(27, "C").Value & " , "
logSTR = logSTR & Cells(28, "C").Value & " , "
logSTR = logSTR & Cells(29, "C").Value & " , "
logSTR = logSTR & Cells(30, "C").Value & " , "
logSTR = logSTR & Cells(31, "C").Value & " , "
logSTR = logSTR & Cells(32, "C").Value & " , "

If there is no data resulting from logSTR = logSTR & Cells(31, "C").Value & " , " I would like to have the data from logSTR = logSTR & Cells(32, "C").Value & " , " placed in that cell rather than leaving a blank. This would allow the data's format (3 values separated by a comma) line up with Header11, Header12 and Header13 (and not Header12, Header13 and No Header).

logSTR = logSTR & Chr(13)
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & Cells(36, "C").Value & " , "
logSTR = logSTR & Cells(37, "C").Value & " , "
logSTR = logSTR & Cells(38, "C").Value & " , "
logSTR = logSTR & Cells(39, "C").Value & " , "
logSTR = logSTR & Cells(40, "C").Value & " , "
logSTR = logSTR & Cells(41, "C").Value & " , "
logSTR = logSTR & Cells(42, "C").Value & " , "
logSTR = logSTR & Chr(13)
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & Cells(46, "C").Value & " , "
logSTR = logSTR & Cells(47, "C").Value & " , "
logSTR = logSTR & Cells(48, "C").Value & " , "
logSTR = logSTR & Cells(49, "C").Value & " , "
logSTR = logSTR & Cells(50, "C").Value & " , "
logSTR = logSTR & Cells(51, "C").Value & " , "
logSTR = logSTR & Cells(52, "C").Value & " , "

Open "Z:\SHARED DRIVE\RequestDirectory\" & ThisWorkbook.Name & ".csv" For Append As #My_filenumber
    Print #My_filenumber, logSTR
Close #My_filenumber


End Sub

Just use an If statement to weed out the blanks e.g If Cells(52, "C") <>"" then logSTR = logSTR & Cells(52, "C").Value & " , " — Absinthe

user3598756 user3598756 · Accepted Answer · 2016-11-14T16:57:42

you could use a helper function like follows:

Function BuildValuesString(colIndex As String, rows As String) As String
    Dim val As Variant

    For Each val In Split(rows, ",")
        If Cells(val, colIndex) <> "" Then BuildValuesString = BuildValuesString & Cells(val, colIndex).Value & " , "
    Next val
End Function

and exploit it in your main code where, for instance:

logSTR = logSTR & Cells(18, "C").Value & " , "
logSTR = logSTR & Cells(19, "C").Value & " , "
logSTR = logSTR & Cells(20, "C").Value & " , "
logSTR = logSTR & Cells(21, "C").Value & " , "
logSTR = logSTR & Cells(22, "C").Value & " , "
logSTR = logSTR & Cells(26, "C").Value & " , "
logSTR = logSTR & Cells(27, "C").Value & " , "
logSTR = logSTR & Cells(28, "C").Value & " , "
logSTR = logSTR & Cells(29, "C").Value & " , "
logSTR = logSTR & Cells(30, "C").Value & " , "
logSTR = logSTR & Cells(31, "C").Value & " , "
logSTR = logSTR & Cells(32, "C").Value & " , "

would become:

logSTR = logSTR & BuildValuesString("C", "18,19,20,21,22,26,27,28,29,30,31,32")

and, in the same fashion you could use other two helpers:

Function BuildNullStrings(numNullStrings As Long) As String
    Dim iNullStrings As Long

    For iNullStrings = 1 To numNullStrings
        BuildNullStrings = BuildNullStrings & "" & " , "
    Next iNullStrings
End Function

Function BuildHeadersString(maxHeader As Long) As String
    Dim iHeader As Long

    For iHeader = 1 To maxHeader
        BuildHeadersString = BuildHeadersString & "Header" & iHeader & " , "
    Next iHeader
End Function

so that your whole code would shorten down to:

Sub WriteCSVFile2()

    Dim My_filenumber As Integer
    Dim logSTR As String

    My_filenumber = FreeFile

    logSTR = logSTR & BuildHeadersString(13)
    logSTR = logSTR & Chr(13)

    logSTR = logSTR & BuildValuesString("C", "18,19,20,21,22,26,27,28,29,30,31,32")

    logSTR = logSTR & Chr(13)
    logSTR = logSTR & BuildNullStrings(5)

    logSTR = logSTR & BuildValuesString("C", "36,37,38,39,40,41,42")

    logSTR = logSTR & Chr(13)
    logSTR = logSTR & BuildNullStrings(5)

    logSTR = logSTR & BuildValuesString("C", "46,47,48,49,50,51,52")


    Open "Z:\SHARED DRIVE\RequestDirectory\" & ThisWorkbook.Name & ".csv" For Append As #My_filenumber
        Print #My_filenumber, logSTR
    Close #My_filenumber


End Sub

Ignoring Blank Cells in CSV Export of Excel Worksheet Data

1 Answers