Forum Discussion

Brass Contributor

Jun 18, 2021

Solved

Excel Spill Row Split

I am wondering if there is away to Split the Spill that comes from the Following ARRAY. =SORT(FILTER(FILTER(TableHLP,TableHLP[District]=$A$7),{1,1,0,0,0,0,0,0}),1) It produces a list of names and s...

excel

Formulas and Functions

Jun 19, 2021

On each sheet you may use formulas like

=LET(k, SEQUENCE(ROWS(array)),
     r, IF( (k >= minN)*(k <= maxN),
            INDEX(array,k),
            "remove"),
     FILTER(r, r <> "remove"))

dshawSLDC

Brass Contributor

Jun 25, 2021

SergeiBaklan

Yeah that did it. Much more elegant than my solution. LOL. Have you ever messed with macros? I'm working on the same file trying to create a print button that will cycle thru each district and print the sheet if there are names returned. So far I have the buttons created and can print the single district. But I'm struggling on the code to make it cycle thru the district list. I attached the example If this is something you are able to assist me in. or pointing me to the right direction. Thanks again for your help.

Example 10.xlsm30 KB

dshawSLDC

Brass Contributor

Jun 25, 2021

@Sergei Baklan

So just in case this is what I have so far. I was able to get it to print all but it also prints the ones that are blank. is there away to tell it if it does not have data to not print that page?

Sub PrintAllDistricts()
    Dim xRg As Range
    Dim xCell As Range
    Dim xRgVList As Range
    Set xRg = Worksheets("Report Select").Range("B1")
    Set xRgVList = Evaluate(xRg.Validation.Formula1)
    For Each xCell In xRgVList
        xRg = xCell.Value
        Call Module1.PrintDistrict
    Next
End Sub

Sub PrintDistrict()
Sheets(Array("Page1", "Page2")).Select
Sheets("Page1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets(Array("Page1", "Page2")).Select
Sheets("Report Select").Activate
Range("B1").Select
End Sub