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.
 
OK, this is a really dumb question but how is there a special way I'm supposed to do that? Here is just a straight copy and paste. BTW, there is formulas in the blank cells. Sorry I didn't mention that earlier. Not sure how I'd copy those into this forum post. Thanks again for all your help.

[TABLE="width: 148"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]QTY[/TD]
[TD]PART[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]6291[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3869[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3871[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2537[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6135[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1423[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6350[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6310[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4982[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]4981[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4994[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2358[/TD]
[/TR]
[TR]
[TD]160[/TD]
[TD]2384[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]5036[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2431[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2388[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]2393[/TD]
[/TR]
[TR]
[TD]74[/TD]
[TD]2396[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]4999[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]2385[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1791[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6369[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Thanks, it indeed works. I looked over the code and the only difference is the following line -
Code:
Set curWS = curWB.Worksheets("Sheet1")

I'm assuming it has to do with that. Could you explain a little bit about what this piece of code is doing? I originally left it as sheet1 but not having a sheet1 in my workbook it threw up the error "Run-time error '9': Subscript out of range.

Is there something in sheet1 that's needed for this to function correctly?
 
Upvote 0
OK, I realized that the sheet1 line is not it, as I changed your sample file to sheet5 and it worked just fine. I'm stumped. I copied and pasted your code and only changed the sheet name to the one I'm working on and still no go. I've exported the necessary sheets with my macro here - https://drive.google.com/file/d/0BwnbsOyA7QwiZzlJVlVmUDdDVFk/view?usp=sharing
Maybe you could look at my file and see what I'm doing wrong?
 
Upvote 0
Ok your empty spaces actually output " " not "" so it technically its not empty.

instead of having;

If cell value = ""

I have put;

if cell value is a number


Code:
Sub Button1_Click()






Dim curWB As Workbook
Set curWB = ActiveWorkbook
Dim curWS As Worksheet


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("NETSUITE CSV").Range("A1:A" & curWB.Worksheets("NETSUITE CSV").Cells(Rows.Count, "A").End(xlUp).Row)
    
        If VBA.IsNumeric(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

How about that?
 
Upvote 0
Ok your empty spaces actually output " " not "" so it technically its not empty.

instead of having;

If cell value = ""

I have put;

if cell value is a number


Code:
Sub Button1_Click()






Dim curWB As Workbook
Set curWB = ActiveWorkbook
Dim curWS As Worksheet


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("NETSUITE CSV").Range("A1:A" & curWB.Worksheets("NETSUITE CSV").Cells(Rows.Count, "A").End(xlUp).Row)
    
        If VBA.IsNumeric(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

How about that?

Hey,
I just discovered that myself after some trial and error. Before I saw your post I ended up changing the if statement to this:
Code:
 If a.Value <> "" And a.Value <> " " And a.Value <> 0 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

This worked. Thank you so much for all of your help!
 
Upvote 0
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


Hi All,

I'm a newbie at VB code, I'm receiving a Application-defined or object-defined error error '1004"
The debug stops at the for loop, syntax looks OK, am I missing something?

Ian
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
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