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!