Macro to save non empty cell data to csv file

[asgjim]

I've been tasked with taking a system design tool and exporting the data in a specific format. It should be fairly easy but I'm completely new when it comes to macros. Basic objective is as follows:
We have a 3 column worksheet with headers. Item, QTY, external id. I need to loop through each row, if column a (item) is blank, don't include the row in the export. Once it's finished the loop, it should export the worksheet as a csv file (with specific header names specified in the code). Ideally, this would be done on a button click on the worksheet. I've found several examples of macros that delete rows with empty cells, create a csv file from a worksheet, but non that really combine these.

The other important part is it should NOT alter the excel file / worksheets in any way. Just create the correctly formatted csv file.

Any help starting or pointing me in the right direction would be greatly appreciated. Let me know what other information I could provide that would help.

 

[lewisgmorris]

Sub copyDataAndGenerateCSV()


Dim curWB As Workbook
Set curWB = ActiveWorkbook
Dim curWS As Worksheet
Set curWS = curWB.Worksheets("Sheet1")


Dim newWB As Workbook
Set newWB = Workbooks.Add


Dim destfolder As String
Dim filename As String


destfolder = curWB.Path & "\"
filename = "output.csv"


Dim a As Range
Dim i As Integer


    For Each a In curWB.Worksheets("Sheet5").Range("A1:A" & curWB.Worksheets("Sheet5").Cells(Rows.Count, "A").End(xlUp).Row)
    
        If a.Value <> "" Then
            
            newWB.Sheets(1).Range("A1").Offset(i, 0) = a.Value
            newWB.Sheets(1).Range("A1").Offset(i, 1) = a.Offset(0, 1).Value
            newWB.Sheets(1).Range("A1").Offset(i, 2) = a.Offset(0, 2).Value
            i = i + 1
        
        End If
    
    Next a




newWB.Sheets(1).Columns.AutoFit


Application.DisplayAlerts = False
newWB.SaveAs destfolder & filename, xlCSV
newWB.Close
MsgBox "CSV File Generated to " & destfolder & filename
Application.DisplayAlerts = True


End Sub

 

[lewisgmorris]

Give that a go, you will just have to change this section

[COLOR=#333333]For Each a In curWB.Worksheets("Sheet5").Range("A1:A" & curWB.Worksheets("Sheet5").Cells(Rows.Count, "A").End(xlUp).Row)
[/COLOR]

Where it says "Sheet5" put your sheet name.

 

[asgjim]

Thanks! I tried you code and it basically exports exactly what's already in the sheet as the csv file. In other words, it's exporting the empty cells / rows into the csv file.

 

[lewisgmorris]

Not on mine its not! You specified you wanted to remove the blanks? Correct?

 

[lewisgmorris]

Is the data in column A?

 

[asgjim]

Is the data in column A?

Yes, but the first row is empty. Not sure if that's causing an issue? The screenshot attached is the original worksheet, but the output file looks identical.

EDIT* the goal is to basically remove lines like row 8, 11, etc... Any row where column A is empty, remove that entire row from the export. I realize there is only 2 columns but there will be a third.

 

[lewisgmorris]

Thats what my code does? works fine this end. Did you check the generated file? Becuase it copies the data to a new workbook saves as CSV then CLOSES IT.

So it may look like nothings happened.

 

[asgjim]

Thats what my code does? works fine this end. Did you check the generated file? Becuase it copies the data to a new workbook saves as CSV then CLOSES IT.

So it may look like nothings happened.

Yup, sorry let me clarify. The screenshot is the original worksheet. I click the button and it creates the csv file a displays the MsgBox. When I open the csv file it copies over all the blank cell rows.

I did modify your code, maybe I broke something. You initially said change sheet 5 to my sheet name. I did that twice here:

For Each a In curWB.Worksheets("NETSUITE CSV").Range("A1:A" & curWB.Worksheets("NETSUITE CSV").Cells(Rows.Count, "A").End(xlUp).Row)

But also where it said sheet1 at the top -

Set curWS = curWB.Worksheets("NETSUITE CSV")

When I left it as sheet 1 the script didn't work.

 

[lewisgmorris]

Those cells might have a space in them then, which would appear empty but are not.

My code does not copy over blank lines

Actually copy and paste your data in here so i can test it.