Export sheet from Excel to CSV

Question

I am creating a spread sheet to help ease the entry of data into one of our systems. They are entering inventory items into this spread sheet to calculate the unit cost of the item (item cost + tax + S&H). The software we purchased cannot do this.

Aan invoice can have one or more lines (duh!) and I calculate the final unit cost. This is working fine. I then want to take that data and create a CSV from that so they can load it into our inventory system. I currently have a second tab that is laid out like I want the CSV, and I do an equal cell (=Sheet!A3) to get the values on the "export sheet". The problem is when they save this to a CSV, there are many blank lines that need to be deleted before they can upload it. I want a file that only contains the data that is needed.

I am sure this could be done in VBA, but I don't know where to start or know how to search for an example to start. Any direction or other options would be appreciated.

Answer 1

expanding on @dwo's answer - this code will allow you to both remove the blank rows and export to CSV:

Sub export_to_csv(sheetname As String, OutDir As String)
    Sheets(sheetname).Select
    Set wb = ActiveWorkbook
    Set newwb = Workbooks.Add()
    wb.ActiveSheet.Copy newwb.ActiveSheet 'copy sheet to new workbook
    newwb.ActiveSheet.Activate
    ActiveSheet.UsedRange 'refresh the used range
    Range("A1:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Select 'select relevant cells
    Selection.SpecialCells(xlBlanks).EntireRow.Delete 'remove empty rows
    Application.DisplayAlerts = False 'avoid warning message when overwriting existing files
    newwb.SaveAs OutDir & sheetname, xlCSVWindows 'save as CSV
    newwb.Close 'close new workbook
    Application.DisplayAlerts = True 'reset warning messages
End Sub

Sub test()
    export_to_csv sheetname:="Sheet1", OutDir:="C:\temp\"
End Sub

Answer 2

The solution on this page worked best for me. I just had to modify it to allow it to work for what I needed.

Answer 3

Look at Range.SpecialCells(xlBlanks).EntireRow.Delete, I think this is what you are looking for!