1

I have created an Excel (2010) workbook that takes information from various CSV files and puts them into a format that can be understood by my office's scheduling program. Using a macro I found online, I can save each sheet of the workbook as a separate CSV file, ready to import.

The problem is that many rows of the workbook are by necessity empty of information, since each row is devoted to a block of time that may or may not be scheduled. These zero-filled rows cause the scheduler program to spit out errors. This isn't a fatal problem, as "good" schedule entries are still imported, but it means I have to wade through hundreds of expected errors to find actual errors.

I was wondering if there was a way to export only certain rows of a sheet to CSV - for example, every row in which the contents of column A are non-zero.

For reference, here is the macro I am currently using to turn the worksheets into separate CSV files.

Option Explicit
Public Sub WriteCSV()

    Dim iFile As Integer
    Dim strText As String, strFileName As String
    Dim lngCol As Long, lngRow As Long
    Dim wks As Worksheet

    iFile = FreeFile()

    For Each wks In ActiveWorkbook.Worksheets
        If wks.Visible = xlSheetVisible Then
            strFileName = ActiveWorkbook.Path & "\" & wks.Name & ".csv"
            Open strFileName For Output As #iFile
                For lngRow = 1 To wks.UsedRange.Rows.Count
                    For lngCol = 1 To wks.UsedRange.Columns.Count
                        Print #iFile, wks.Cells(lngRow, lngCol).Text & ",";
                    Next lngCol
                        Print #iFile,
                Next lngRow
            Close #iFile
        End If
    Next wks

End Sub

Thanks!

1 Answer 1

0

After For lngRow = 1 to wks.UsedRange.Rows.Count, add this line:

If Not IsNull(wks.Cells(l, 1).Value) And Trim(wks.Cells(l, 1).Value) <> "" Then

Note that there is a lowercase "L", then a number one in the parenthesis. Then, before Next lngRow, put this line in:

End If

That should skip any rows with either null (nothing) or empty (i.e., only tabs or spaces) in the first column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.