Exporting Values From Tab to CSV - Need to Omit Rows with No Values (but has formula)

snowmangoh

snowmangoh

New Member
Joined
Oct 24, 2006
Messages
8
First of all, let me apologize because I have seen similar posts, but couldn't extract from them what I need... Certainly an intelligence issue on my end.
I am trying to export a tab to a csv. The entire tab is populated via formulas, that are essentially starting at a column where I am indexing only non-blank values. That csv will then be used to import data to a DB. My current VBA that is exporting the tab is:
VBA Code: 
Sub Export_ePIF_NEW_A()
    Dim NewFN As String
    
    NewFN = ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & " - EPIF_CM_TEMPLATE_NEW-A (" & Format(Date, "DD-MMM-YYYY") & ")" & ".csv"
  
  Debug.Print NewFN
 
    Sheets("epif_cm_template_NEW-A").Copy
    With ActiveSheet
        .UsedRange.Copy
        .Cells(1, 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=True, Transpose:=False
        .Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
    
    Range("A1:A1").Select
    
    Application.CutCopyMode = False
    
    With ActiveWorkbook
        .SaveAs NewFN, xlCSV
        .Close False
    End With
End Sub

This works (almost) as expected, however it exports all "blank" rows (there are formulas there of course), and I wind up with a few hundred rows that are just 20 commas. Is there an easy fix in my code so that it doesn't paste those "blank" rows? In case it matters, the macro is triggered with a button on another tab.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Maybe something like this.

VBA Code: 
Sub Export_ePIF_NEW_A()
    Dim NewFN As String
    
    NewFN = ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & " - EPIF_CM_TEMPLATE_NEW-A (" & Format(Date, "DD-MMM-YYYY") & ")" & ".csv"
    
    Debug.Print NewFN
    
    Sheets("epif_cm_template_NEW-A").Copy
    With ActiveSheet
        .UsedRange.Copy
        .Cells(1, 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=True, Transpose:=False
        .Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
    
    Range("A1:A1").Select
    
    Call DeleteEmptyRows(Range("A1").Parent)
    
    Application.CutCopyMode = False
    
    With ActiveWorkbook
        .SaveAs NewFN, xlCSV
        .Close False
    End With
End Sub



VBA Code: 
Sub DeleteEmptyRows(WS As Worksheet)
    Dim rng As Range, HR As Range, R As Range, DelRange As Range
    Dim LastColUsed As Long
    
    With WS
        .AutoFilterMode = False
        Set rng = Application.Intersect(.UsedRange, .Range("A1").EntireColumn)
        LastColUsed = .UsedRange.Columns.Count
        
        For Each R In rng
            Set HR = R.Resize(1, LastColUsed - 1)
            R.Offset(0, LastColUsed).Value = Evaluate("=CountA(" & HR.Address & ")")
        Next R
        
        With .UsedRange
            Set rng = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count)
            .AutoFilter Field:=.Columns.Count, Criteria1:="0"
        End With
        
        On Error Resume Next
        Set DelRange = rng.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        If Not DelRange Is Nothing Then
            DelRange.EntireRow.Delete
        End If
        
        .AutoFilterMode = False
        .Columns(.UsedRange.Columns.Count).Delete
    End With
End Sub
 
Upvote 0
rlv01 said:
Maybe something like this.

VBA Code: 
Sub Export_ePIF_NEW_A()
    Dim NewFN As String
  
    NewFN = ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & " - EPIF_CM_TEMPLATE_NEW-A (" & Format(Date, "DD-MMM-YYYY") & ")" & ".csv"
  
    Debug.Print NewFN
  
    Sheets("epif_cm_template_NEW-A").Copy
    With ActiveSheet
        .UsedRange.Copy
        .Cells(1, 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=True, Transpose:=False
        .Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
  
    Range("A1:A1").Select
  
    Call DeleteEmptyRows(Range("A1").Parent)
  
    Application.CutCopyMode = False
  
    With ActiveWorkbook
        .SaveAs NewFN, xlCSV
        .Close False
    End With
End Sub



VBA Code: 
Sub DeleteEmptyRows(WS As Worksheet)
    Dim rng As Range, HR As Range, R As Range, DelRange As Range
    Dim LastColUsed As Long
  
    With WS
        .AutoFilterMode = False
        Set rng = Application.Intersect(.UsedRange, .Range("A1").EntireColumn)
        LastColUsed = .UsedRange.Columns.Count
      
        For Each R In rng
            Set HR = R.Resize(1, LastColUsed - 1)
            R.Offset(0, LastColUsed).Value = Evaluate("=CountA(" & HR.Address & ")")
        Next R
      
        With .UsedRange
            Set rng = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count)
            .AutoFilter Field:=.Columns.Count, Criteria1:="0"
        End With
      
        On Error Resume Next
        Set DelRange = rng.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
      
        If Not DelRange Is Nothing Then
            DelRange.EntireRow.Delete
        End If
      
        .AutoFilterMode = False
        .Columns(.UsedRange.Columns.Count).Delete
    End With
End Sub
Click to expand...
Thanks for the reply @rlv01! It still outputs the blank lines of commas... It also took a couple of minutes to run, but I'm assuming that it was checking the entire csv, so I guess that makes sense.
 

Attachments

  • csv export example2.png
    csv export example2.png
    28.5 KB · Views: 7
Upvote 0
You should use the XL2BB add-in to post an example of what your worksheet data looks like just before you save it to a csv file.

www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

e.g.
Book1
ABCDEFGHIJ
1Header1Header2Header3Header4Header5Header6Header7Header8Header9Header10
2Update152AAA1XXX
3Update152BBB2XXXNN
4Update152CCC3AB123XXX
5
6
7
8Update152GGGYYY
9Update152HHHZZZ
10
11
12
13
14
15
16
Sheet1
 
Upvote 0
Thanks! Doing that now... I selected too many columns and so now I'm waiting for it to calm down, lol... I'll kill it in a minute and select fewer... the idea will remain the same.
 
Upvote 0
rlv01 said:
You should use the XL2BB add-in to post an example of what your worksheet data looks like just before you save it to a csv file.

www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

e.g.
Book1
ABCDEFGHIJ
1Header1Header2Header3Header4Header5Header6Header7Header8Header9Header10
2Update152AAA1XXX
3Update152BBB2XXXNN
4Update152CCC3AB123XXX
5
6
7
8Update152GGGYYY
9Update152HHHZZZ
10
11
12
13
14
15
16
Sheet1
Click to expand...
I've rebooted, and decreased the range I was capturing greatly, but it is still locking up my Excel... I'll leave it spinning for a while, but it's hurting.
 
Upvote 0
Okay... I unclicked a bunch of options and it worked... Here it is. Thanks @rlv01!

__host1 to host2 migration example.xlsm
ABCDEFGH
1ChangeNumberTypeSite TypeIDconnectivityActionCable Type
2ADD6236Cabling OnlyData Center101HOST2_E1-45\HOST25_E1-mgmt0New CableCAT6
3ADD6236Cabling OnlyData Center102HOST2_E1-46\HOST60_E0-mgmt0New CableCAT6
4ADD6236Cabling OnlyData Center103HOST2_E1-47\HOST48_E0-mgmt0New CableCAT6
5ADD6236Cabling OnlyData Center105HOST3_E1-48\HOST0200_E1-mgmt0New CableCAT6
6        
7        
8        
9        
10        
11        
12        
epif_cm_template_NEW-A
Cell Formulas
RangeFormula
A2:A12A2=IF(E2="","", IF('2 Site Overview'!$B$14="NO","ADD", IF('2 Site Overview'!$B$14="YES","UPDATE", "COMPLETE EPIF INFO")))
B2:B12B2=IF(E2="","",'2 Site Overview'!$B$15)
C2:C12C2=IF(E2="","",'2 Site Overview'!$B$16)
D2:D12D2=IF(E2="","",'2 Site Overview'!$B$17)
E2:E12E2=IFERROR(INDEX('3 Cable Matrix'!$BU$1:$BU$1000, SMALL( IF('3 Cable Matrix'!$O$1:$O$1000<>"", IF('3 Cable Matrix'!$O$1:$O$1000<>"NO", IF('3 Cable Matrix'!$O$1:$O$1000<>"N/A", IF('3 Cable Matrix'!$O$1:$O$1000<>"DECOM", ROW('3 Cable Matrix'!$BU$1:$BU$1000))))),ROW(1:1)+1)),"")
F2:F12F2=IF(G2="","", IF(OR(G2="New Cable",G2="Relabel"),INDEX('3 Cable Matrix'!$Q:$Q,MATCH(E2,'3 Cable Matrix'!$BU:$BU,0)),"NEED TO EVALUATE"))
G2:G12G2=IF(E2="","", IF(OR(INDEX('3 Cable Matrix'!$O:$O,MATCH(E2,'3 Cable Matrix'!$BU:$BU,0))="NEW",INDEX('3 Cable Matrix'!$O:$O,MATCH(E2,'3 Cable Matrix'!$BU:$BU,0))="NEW - CABLE TEST",'2 Site Overview'!$B$13="MIGRATION",'2 Site Overview'!$B$13="CHASSIS SWAP/MOVE"),"New Cable", IF(OR(INDEX('3 Cable Matrix'!$O:$O,MATCH(E2,'3 Cable Matrix'!$BU:$BU,0))="YES"),"Relabel", IF(OR(INDEX('3 Cable Matrix'!$O:$O,MATCH(E2,'3 Cable Matrix'!$BU:$BU,0))="DECOM",INDEX('3 Cable Matrix'!$O:$O,MATCH(E2,'3 Cable Matrix'!$BU:$BU,0))="NO"),"NEED TO EVALUATE (SHOULDN'T BE ON THIS PAGE)"))))
H2:H12H2=IF(G2="","", IF(OR(G2="New Cable",G2="Relabel"),INDEX('3 Cable Matrix'!$X:$X,MATCH(E2,'3 Cable Matrix'!$BU:$BU,0)),"NEED TO EVALUATE"))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
A couple of things:
1) There are no blank cells or rows in the above. If I save that data to a csv file and open it in Notepad, what I see is this:

Change,Number,Type,Site Type,ID,connectivity,Action,Cable Type,
ADD,6236,Cabling Only,Data Center,101,HOST2_E1-45\HOST25_E1-mgmt0,New Cable,CAT6,
ADD,6236,Cabling Only,Data Center,102,HOST2_E1-46\HOST60_E0-mgmt0,New Cable,CAT6,NN
ADD,6236,Cabling Only,Data Center,103,HOST2_E1-47\HOST48_E0-mgmt0,New Cable,CAT6,
ADD,6236,Cabling Only,Data Center,105,HOST3_E1-48\HOST0200_E1-mgmt0,New Cable,CAT6,

Everything as expected and no lines with only commas (",,,,,,,,,,,,,,,,,,,,,,,,,"). What I would like to see is data that produces that problem for you.

2) The presence of formulas in the above leads me to believe you are posting your data from your original source sheet before you copy it to your new workbook. That is not precisely what I was after. In your original code you have this line

VBA Code: 
      .Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

which should convert any formulas to values prior to saving the new workbook as a .csv file. That's the data I was interested in seeing so I could understand how the rows of only-commas are being produced when you do the save.
 
Upvote 0
rlv01 said:
A couple of things:
1) There are no blank cells or rows in the above. If I save that data to a csv file and open it in Notepad, what I see is this:

Change,Number,Type,Site Type,ID,connectivity,Action,Cable Type,
ADD,6236,Cabling Only,Data Center,101,HOST2_E1-45\HOST25_E1-mgmt0,New Cable,CAT6,
ADD,6236,Cabling Only,Data Center,102,HOST2_E1-46\HOST60_E0-mgmt0,New Cable,CAT6,NN
ADD,6236,Cabling Only,Data Center,103,HOST2_E1-47\HOST48_E0-mgmt0,New Cable,CAT6,
ADD,6236,Cabling Only,Data Center,105,HOST3_E1-48\HOST0200_E1-mgmt0,New Cable,CAT6,

Everything as expected and no lines with only commas (",,,,,,,,,,,,,,,,,,,,,,,,,"). What I would like to see is data that produces that problem for you.

2) The presence of formulas in the above leads me to believe you are posting your data from your original source sheet before you copy it to your new workbook. That is not precisely what I was after. In your original code you have this line

VBA Code: 
      .Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

which should convert any formulas to values prior to saving the new workbook as a .csv file. That's the data I was interested in seeing so I could understand how the rows of only-commas are being produced when you do the save.
Click to expand...
"The presence of formulas in the above leads me to believe you are posting your data from your original source sheet before you copy it to your new workbook." - I am posting the data from the sheet that the macro exports...
"If I save that data to a csv file and open it in Notepad" - You are correct! Furthermore, if I open the exported csv file that has the rows of commas in excel and save and close it, I can see it has removed the lines if you view it in a text editor... The issue is clearly that is says "Okay, you want to ignore blanks! These cells are not blank because they have formulas in them... so let me just put these formulated values of "" in here for these 1000 rows."

I learn the VBA/coding I need as I need it, so this might not make sense... but this feels like the kind of thing that would be solved by saving those "values" as an array/variable, at which point they really would be literal blanks, and then pasting THAT into my new sheet while skipping blanks.
 
Upvote 0
snowmangoh said:
I am posting the data from the sheet that the macro exports...
Click to expand...

I ran your posted code:
VBA Code: 
    With ActiveSheet
        .UsedRange.Copy
        .Cells(1, 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
        .Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
and as expected, the worksheet being saved to a csv file contains no formulas, only values, which is the expected behavior. So if there are still formulas in the worksheet that you save, something unexplained is going on.

Try this alternative.
VBA Code: 
Sub Export_ePIF_NEW_A()
    Dim NewFN As String
    Dim CSVWB As Workbook
    Dim WS As Worksheet
    Dim VA As Variant

    NewFN = ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & " - EPIF_CM_TEMPLATE_NEW-A (" & Format(Date, "DD-MMM-YYYY") & ")" & ".csv"

    Sheets("epif_cm_template_NEW-A").Copy
    Set CSVWB = ActiveWorkbook
    Set WS = CSVWB.Sheets(1)

    With WS
        VA = .UsedRange.Value
         .UsedRange.Value = VA
    End With

    Application.ScreenUpdating = False
    Call DeleteEmptyRows(WS)
    Application.ScreenUpdating = True
    
    With CSVWB
        .SaveAs NewFN, xlCSV
        .Close False
    End With
End Sub


VBA Code: 
Sub DeleteEmptyRows(WS As Worksheet)
    Dim I As Long, J As Long
    Dim DelRange As Range, rng As Range
    Dim S As String
    Dim VA As Variant

    Set rng = WS.UsedRange.Resize(, WS.UsedRange.Columns.Count + 1)

    VA = rng.Value

    For I = LBound(VA, 1) To UBound(VA, 1)            'row
        S = ""
        For J = LBound(VA, 2) To UBound(VA, 2) - 1    'col
            S = S & Trim(VA(I, J))
        Next J
        If S = "" Then
            VA(I, UBound(VA, 2)) = 0
        End If
    Next I

    rng.Value = VA

    With WS
        .AutoFilterMode = False
        With .UsedRange
            Set rng = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count)
            .AutoFilter Field:=.Columns.Count, Criteria1:="0"
        End With

        On Error Resume Next
        Set DelRange = rng.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0

        If Not DelRange Is Nothing Then
            DelRange.EntireRow.Delete
        End If

        .AutoFilterMode = False
        .Columns(.UsedRange.Columns.Count).Delete
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
211
kbk
K
S
  • Question Question
CSV export to remove column
Replies
1
Views
633
StellarRumble59
S
C
  • Question Question
How to automatically paste the data from one sheet to another using VBA
Replies
4
Views
235
Alex Blakenburg
A
C
  • Question Question
Excel VBA CSV export query
Replies
1
Views
545
cerberus1845
C
B
  • Question Question
VBA exports worksheet from XLSM to XLXS matching all source properties doesn't keep print area
Replies
0
Views
100
brentcole
B

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

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