I have a excel file with template which look something like this.

I filter down records based on Customer ID column and save them as individual Pdf's.I am using below VBA code to do the job.

Public Sub Create_PDFs()

    Dim CustomerIDsDict As Object, CustomerID As Variant
    Dim r As Long
    Dim currentAutoFilterMode As Boolean
    
    Set CustomerIDsDict = CreateObject("Scripting.Dictionary")
    
    'The code looks at data on the active sheet
    
    With ActiveSheet
    
        'Save current UI autofilter mode
        
        currentAutoFilterMode = .AutoFilterMode
        
        If currentAutoFilterMode Then .AutoFilter.ShowAllData
       
        'Create dictionary containing unique Customer IDs (column B) and associated Country (column B), keyed on Customer ID
        
        For r = 5 To .Cells(.Rows.Count, "B").End(xlUp).Row
            CustomerIDsDict(.Cells(r, "B").Value) = .Cells(r, "C").Value
        Next
        
        'For each unique Customer ID
        
        For Each CustomerID In CustomerIDsDict.keys
            
            'AutoFilter on column B (Field:=2) with this Customer ID
            
            '.UsedRange.AutoFilter Field:=2, Criteria1:=CustomerID
             With .Range("A3")
                .AutoFilter Field:=2, Criteria1:=CustomerID
                .Rows(2).EntireRow.Hidden = False
             End With

            'Save filtered data as PDF file "<Customer ID> <Country>.pdf" in same folder as this workbook
            
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & CustomerID & " " & CustomerIDsDict(CustomerID) & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
                    
        Next
    
        'Restore previous autofilter, if any
    
        If currentAutoFilterMode Then
            .AutoFilter.ShowAllData
        Else
            .AutoFilterMode = False
        End If
        
    End With
    
End Sub

But certain CustomerID's don't have any values in last six columns. namely New volume Qty,New volume price these columns. Before generating PDF's I want to check these columns if anyone of records as values then we can show those columns else need not to show them in Pdf's file.

For example:

If you see for this CustomerID it doesn't have any value in columns New Volume qty2,3,4 and New Volume Price 2,3,4 so in this case I don't want these columns to shown in PDF of this particular customer ID.

Whereas in the below customer ID

Only New volume qty 3,4 and New volume price 3,4 doesn't have value . so I want to remove only these columns before saving them as PDF.

Headers for the mentioned column

Is there way I can do this using the above script. Can anyone help me with this.

CodePudding user response：

Please, test the next code. You did not answer my clarification question and it allows creating the columns range to be checked. Than, creates an array of these columns numbers (arrCols), checks if no any value in each of such columns and place a cell of them in a range (rngHd), for such a case. Then, hides them before exporting and makes them visible after exporting:

Public Sub Create_PDFs()
    Dim CustomerIDsDict As Object, CustomerID As Variant
    Dim r As Long, currentAutoFilterMode As Boolean
    Dim strCols As String, rngHd As Range, lastR As Long, arrCols, i As Long, iRow As Long
    
    strCols = "O:V": arrCols = Evaluate("column(" & strCols & ")") 'place in an array the columns to be checked number
    iRow = 5
    Set CustomerIDsDict = CreateObject("Scripting.Dictionary")
    
    'The code looks at data on the active sheet
    With ActiveSheet
        lastR = .Range("A" & .Rows.count).End(xlUp).row 'last row in A:A
        
        'Save current UI autofilter mode
        currentAutoFilterMode = .AutoFilterMode
        If currentAutoFilterMode Then .AutoFilter.ShowAllData
       
        'Create dictionary containing unique Customer IDs (column B) and associated Country (column B), keyed on Customer ID
        For r = 5 To .cells(.Rows.count, "B").End(xlUp).row
            CustomerIDsDict(.cells(r, "B").Value) = .cells(r, "C").Value
        Next
        
        'For each unique Customer ID
        For Each CustomerID In CustomerIDsDict.Keys
            
            'AutoFilter on column B (Field:=2) with this Customer ID
             With .Range("A3")
                .AutoFilter field:=2, Criteria1:=CustomerID
                .Rows(2).EntireRow.Hidden = False
             End With
             
             'place the empty columns one cell in a Union range
             For i = 1 To UBound(arrCols)
                 If WorksheetFunction.CountA(.Range(.cells(iRow, arrCols(i)), .cells(lastR, arrCols(i))).SpecialCells(xlCellTypeVisible)) = 0 Then
                     If rngHd Is Nothing Then
                        Set rngHd = .cells(3, arrCols(i))
                     Else
                        Set rngHd = Union(rngHd, .cells(3, arrCols(i)))
                     End If
                 End If
            Next i
            'Hide the empty columns, if the case:
            If Not rngHd Is Nothing Then rngHd.EntireColumn.Hidden = True
            
            'Save filtered data as PDF file "<Customer ID> <Country>.pdf" in same folder as this workbook
            .ExportAsFixedFormat Type:=xlTypePDF, filename:=ThisWorkbook.Path & "\" & CustomerID & " " & CustomerIDsDict(CustomerID) & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
                
            rngHd.EntireColumn.Hidden = False 'make all checked columns visible
            Set rngHd = Nothing                    'reSet the range as Nothing for the future iterations
        Next
    
        'Restore previous autofilter, if any
        If currentAutoFilterMode Then
            .AutoFilter.ShowAllData
        Else
            .AutoFilterMode = False
        End If
        
    End With
End Sub

No error handling for the case of the column already hidden. Theoretically, such a situation cannot appear in normal using way. It ca be previously check if the column visibility...