Macro to save non empty cell data to csv file

asgjim

New Member
Joined
Oct 24, 2014
Messages
13
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Code:
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
 
Last edited:
Upvote 0
Give that a go, you will just have to change this section

Code:
[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.
 
Upvote 0
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.
 
Upvote 0
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.
BRfjJ76.png


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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:
Code:
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 -
Code:
Set curWS = curWB.Worksheets("NETSUITE CSV")
When I left it as sheet 1 the script didn't work.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top