VBA to remove empty elements from array

[Tanyaann1995]

Hi,

I have created a userform where the user can pick which sheets they want to get printed by clicking on checkboxes. Once the user chooses which sheets they want to print, the code should print out only those sheets using an array. I have designed the code in such a way that if the user picks only certain sheets, the other array values become blank. I need to remove these empty values from the array to print out only the required sheets. Please help.

Sub print()

Dim PDFfile As String
    Dim wksAllSheets() As Variant
    Dim wksSheet1 As Worksheet
    Dim wb As Workbook
    Dim arr1, arr2, arr3, arr4
Set wb = ActiveWorkbook

UserForm5.Show
If UserForm5.CheckBox1.Value = True Then
arr1 = Array("Cover")
Else
arr1 = Array("")
End If
If UserForm5.CheckBox2.Value = True Then
arr2 = Array("Revision")
Else
arr2 = Array("")
End If
If UserForm5.CheckBox5.Value = True Then
arr3 = Array("Emerson COMMERCIAL OFFER")
Else
arr3 = Array("")
End If
If UserForm5.CheckBox4.Value = True Then
arr4 = Array("Technical Offer")
Else
arr4 = Array("")
End If
wksAllSheets = uniqueArr(arr1, arr2, arr3, arr4)


PDFfile = wb.path & "\" & wb.Worksheets(1).Range("F12").Value & ".pdf"
wb.Sheets(wksAllSheets).Select
Set wksSheet1 = wb.Sheets("Cover")
    wksSheet1.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=PDFfile, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              IgnorePrintAreas:=False, _
              OpenAfterPublish:=False
End If
wb.Worksheets(1).Select
End Sub

 

[dmt32]

Hi,
rather than using individual arrays, try building a string of comma delimited sheets & use Split function to return a one-dimensional array.

Not tested but something like following may do what you want

Dim i As Integer

For i = 1 To 4
    If Me.Controls("CheckBox" & i).Value Then
    wksAllSheets = wksAllSheets & Choose(i, "Cover", "Revision", "Technical Offer", _
                                            "Emerson COMMERCIAL OFFER") & ","
    End If
Next

'nothing selected
If Len(wksAllSheets) = 0 Then Exit Sub
'remove last comma
wksAllSheets = Mid(wksAllSheets, 1, Len(wksAllSheets) - 1)
'select sheets
Sheets(Split(wksAllSheets, ",")).Select

'rest of code

Solution will need to be adjusted to meet specific project need

Hope helpful

Dave

 

[Tanyaann1995]

Hi,
rather than using individual arrays, try building a string of comma delimited sheets & use Split function to return a one-dimensional array.

Not tested but something like following may do what you want

Dim i As Integer

For i = 1 To 4
    If Me.Controls("CheckBox" & i).Value Then
    wksAllSheets = wksAllSheets & Choose(i, "Cover", "Revision", "Technical Offer", _
                                            "Emerson COMMERCIAL OFFER") & ","
    End If
Next

'nothing selected
If Len(wksAllSheets) = 0 Then Exit Sub
'remove last comma
wksAllSheets = Mid(wksAllSheets, 1, Len(wksAllSheets) - 1)
'select sheets
Sheets(Split(wksAllSheets, ",")).Select

'rest of code

Solution will need to be adjusted to meet specific project need

Hope helpful

Dave

Hi Dave,

Thanks for the code and it is running well now. However, the printed sheets only include the Cover sheet when other sheets are selected and the technical sheet is being printed instead when the commercial sheet is being selected to print. Pls advise.

 

[dmt32]

Hi Dave,

Thanks for the code and it is running well now. However, the printed sheets only include the Cover sheet when other sheets are selected and the technical sheet is being printed instead when the commercial sheet is being selected to print. Pls advise.

Hi,
Glad to hear suggestion goes in right direction but I did state it would need to be adjusted to meet your project need

- If an incorrect sheet is printing out then that is to do with the indexing each CheckBox in the For Next loop
This is indexed 1 to 4 & selects the worksheet in that order in the Choose List

Choose(i, "Cover", "Revision", "Technical Offer", _
                                            "Emerson COMMERCIAL OFFER")

Solution as published, code refers to each checkbox as follows

CheckBox1 = "Cover"
CheckBox2 = "Revision"
CheckBox3 = "Technical Offer"
CheckBox4 = "Emerson COMMERCIAL OFFER"

If either the order in the Choose list or the Control index is wrong then you need to update it as required

To printout all selected sheets try adding following in your code

'PrintOut all selected sheets
ActiveWindow.SelectedSheets.PrintOut

Dave

 

[Tanyaann1995]

Hi,
Glad to hear suggestion goes in right direction but I did state it would need to be adjusted to meet your project need

- If an incorrect sheet is printing out then that is to do with the indexing each CheckBox in the For Next loop
This is indexed 1 to 4 & selects the worksheet in that order in the Choose List

Choose(i, "Cover", "Revision", "Technical Offer", _
                                            "Emerson COMMERCIAL OFFER")

Solution as published, code refers to each checkbox as follows

CheckBox1 = "Cover"
CheckBox2 = "Revision"
CheckBox3 = "Technical Offer"
CheckBox4 = "Emerson COMMERCIAL OFFER"

If either the order in the Choose list or the Control index is wrong then you need to update it as required

To printout all selected sheets try adding following in your code

'PrintOut all selected sheets
ActiveWindow.SelectedSheets.PrintOut

Dave

Hi Dave,

As you have mentioned, there was an error in the index in the Choose list which I have corrected. Now, the code is working great.
Only problem is I want to print these sheets out and save them in the current folder with a filename taken from the worksheet. This was the code that I used for it earlier:

PDFfile = wb.path & "\" & wb.Worksheets(1).Range("F12").Value & ".pdf"
Set wksSheet1 = wb.Sheets("Cover")
    wksSheet1.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=PDFfile, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              IgnorePrintAreas:=False, _
              OpenAfterPublish:=False

But the above code doesn't work properly. For example, If I have selected other sheets except "Cover", it will only print Cover sheet.
I want to use your code because this works perfectly:

ActiveWindow.SelectedSheets.PrintOut

But, how do I incorporate the filename as current folder and filepath to this?

 

[Peter_SSs]

This is not suggesting a way to print certain sheets, but simply a demonstration of the issue raised in your thread title - filtering out blank values from an array.

Sub Filter_For_Non_Blanks()
    Dim myArray(1 To 7)
    Dim mySearchArray As Variant
    Dim myFilteredArray As Variant
    Dim myMarker As String, myDelimiter As String
   
    'myMarker and myDelimiter must be characters that
    'don't occur in any element of the array!
    myMarker = "#"
    myDelimiter = "|"
   
    'Fill some elements of the array
    myArray(1) = "x,78"
    myArray(3) = 6
    myArray(6) = "abc"
   
    mySearchArray = Split(myMarker & Join(myArray, myMarker & _
                      myDelimiter & myMarker) & myMarker, myDelimiter)
   
    'Filter to EXCLUDE double myMarker values
    myFilteredArray = Filter(mySearchArray, myMarker & myMarker, False, vbTextCompare)
   
    'Remove markers from results
    myFilteredArray = _
        Split(Replace(Join(myFilteredArray, myDelimiter), myMarker, ""), myDelimiter)
   
    'Show results
    MsgBox "Filtering Array(" & Join(myArray, " " & myDelimiter & " ") & _
           ") for non-blanks returned:" & _
           vbCr & Join(myFilteredArray, vbCr)
End Sub

 

[dmt32]

But, how do I incorporate the filename as current folder and filepath to this?

Hi,
you can try placing that code in a For Next Loop to export the selected sheets and see if this will do what you want
You are using a fixed range to hold the filename - in the updated code, I have replaced this with the worksheet name but you should adjust suggestion (shown in BOLD) as required.

Dim ws As Worksheet
For Each ws In Worksheets(Split(wksAllSheets, ","))
    PDFfile = wb.Path & "\" & ws.Name & ".pdf"
    ws.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=PDFfile, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              IgnorePrintAreas:=False, _
              OpenAfterPublish:=False
              
Next

Dave

 

[Tanyaann1995]

Hi,
you can try placing that code in a For Next Loop to export the selected sheets and see if this will do what you want
You are using a fixed range to hold the filename - in the updated code, I have replaced this with the worksheet name but you should adjust suggestion (shown in BOLD) as required.

Dim ws As Worksheet
For Each ws In Worksheets(Split(wksAllSheets, ","))
    PDFfile = wb.Path & "\" & ws.Name & ".pdf"
    ws.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=PDFfile, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              IgnorePrintAreas:=False, _
              OpenAfterPublish:=False
             
Next

Dave

Hi Dave,

Thanks for the above code.
The For loop would only print out the individual sheets separately and save both in the same folder. My previous code would merge both the files also and save the merged file in the same folder. Is there any way to merge also?

 

[dmt32]

Hi,
see if this change does what you want?

PDFfile = wb.Path & "\" & wb.Worksheets(1).Range("F12").Value & ".pdf"
'select sheets
Sheets(Split(wksAllSheets, ",")).Select

 ActiveSheet.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=PDFfile, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              ignoreprintareas:=False, _
              openafterpublish:=False

Dave

 

[Tanyaann1995]

Hi,
see if this change does what you want?

PDFfile = wb.Path & "\" & wb.Worksheets(1).Range("F12").Value & ".pdf"
'select sheets
Sheets(Split(wksAllSheets, ",")).Select

 ActiveSheet.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=PDFfile, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              ignoreprintareas:=False, _
              openafterpublish:=False

Dave

Thanks Dave Everything works perfectly now