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

[snowmangoh]

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:

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.

 

[Alex Blakenburg]

If your blank rows are only at the bottom, you can try adding the below after your Paste Special Values and before your End With

        ' Next 3 lines added to remove blank lines at the bottom
        Dim srcRowLast As Long
        srcRowLast = .Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        .Rows(srcRowLast + 1 & ":" & .UsedRange.Rows.Count).Delete

 

[snowmangoh]

I ran your posted 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.

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

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

So... I see what you're doing (no way I could have done it myself), and I think it's the right idea, but it's getting hung up with Run-time error '1004': Cannot use that command on overlapping sections, and going to debug highlights DelRange.EntireRow.Delete. At that point, the workbook is still open of course, and it has filtered to only show the blanks... I am assuming the intent is to then delete all the rows that have "" as the value? Thank you so much for your continued help!!

 

[snowmangoh]

If your blank rows are only at the bottom, you can try adding the below after your Paste Special Values and before your End With

        ' Next 3 lines added to remove blank lines at the bottom
        Dim srcRowLast As Long
        srcRowLast = .Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        .Rows(srcRowLast + 1 & ":" & .UsedRange.Rows.Count).Delete

That got it!! Thank you! This was literally the last headache before pushing something into production, and I have been staring at Office VBA Reference sheets, and other code windows for three days. You and @rlv01 seriously made my entire week... I hate not being able to figure stuff out. I can sleep again. THANK YOU!

 

[Alex Blakenburg]

I think we all know that feeling. Glad we could help.