生成PDFVBA時洗掉列

我有一個帶有模板的 excel 檔案，看起來像這樣。

我根據客戶 ID 列過濾記錄并將它們保存為單獨的 Pdf。我使用下面的 VBA 代碼來完成這項作業。

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

但是某些 CustomerID 在最后六列中沒有任何值。即新卷數量，新卷價格這些列。在生成 PDF 之前，我想檢查這些列是否有任何記錄作為值，然后我們可以顯示這些列，否則不需要在 Pdf 檔案中顯示它們。

例如：

如果您看到此 CustomerID 它在New Volume qty2,3,4 和 New Volume Price 2,3,4列中沒有任何值，因此在這種情況下，我不希望這些列顯示在此特定客戶的 PDF 中ID。

而在下面的客戶 ID

只有 New volume qty 3,4 和 New volume price 3,4沒有價值。所以我想在將它們保存為 PDF 之前只洗掉這些列。

上述列的標題

有沒有辦法我可以使用上面的腳本來做到這一點。誰能幫我這個。

uj5u.com熱心網友回復：

請測驗下一個代碼。您沒有回答我的澄清問題，它允許創建要檢查的列范圍。然后，創建一個由這些列編號 ( arrCols) 組成的陣列，檢查每個此類列中是否沒有任何值，并將它們中的一個單元格放在一個范圍 ( rngHd) 中，對于這種情況。然后，在匯出前隱藏它們并在匯出后使其可見：

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

對于已經隱藏的列的情況沒有錯誤處理。從理論上講，這種情況在正常使用方式中是不會出現的。可以預先檢查列的可見性...

標籤：

上一篇：在python中按鍵對嵌套的dict串列進行排序

下一篇：在多個相同作業表之間切換資料而不會丟失任何資料