macro to set print area?

[slowbob]

I have a worksheet that's formatted to print out on a long, 1 inch-wide strip of adhesive label tape. The range of potentially printable cells goes from A1 to CF6.

All of the cells in this range have conditional formulas in them to either pull criteria from another sheet in the file, or to be blank, i.e.:

=IF('list'!$M371="","",'list'!$M371)

I'm trying to create a macro that will set the print area to include only cells that don't equal "", then print. I have searched many excel boards and found nothing that will work. I've also tried changing the "" to 0. (This didn't work either, and also messed up the concatenate function I'm using in one of the rows.)

Right now I'm using this, but it still selects A1 to CF6 when I run it:
========
Sub SelectLabels()

Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(0, 1)
Do Until Application.Count(lastCell.EntireRow) <> ""
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address

End Sub
===========

Can anyone give me a clue?

Thanks in advance.

 

[AlphaFrog]

Are trying to exclude empty rows or empty columns in A1:CF6 ?

This will Hide blank Rows\Columns in the range A1:CF6. If your print area is set to print A1:CF6, the hidden rows\columns won't print.

Sub Hide_Blank_RowsCols()
    Dim i As Integer
    
    Application.ScreenUpdating = False
    For i = 1 To 6
        If WorksheetFunction.CountA(Range("A" & i, "CF" & i)) = 0 Then Rows(i).Hidden = True
    Next i
    For i = 1 To Columns("CF").Column
        If WorksheetFunction.CountA(Range(Cells(1, i), Cells(6, i))) = 0 Then Columns(i).Hidden = True
    Next i
    Application.ScreenUpdating = True
    
End Sub

Sub UnHide_RowsCols()

    Application.ScreenUpdating = False
    Rows("1:6").Hidden = False
    Columns("A:CF").Hidden = False
    Application.ScreenUpdating = True
    
End Sub

 

[AlphaFrog]

Correction: I realized that the above macro doesn't work when a formula returns "". Give this a try. It's a bit of a kludge, but it seems to work.

Sub Hide_Blank_RowsCols()
    Dim r As Integer, c As Integer, bFlag As Boolean

    Application.ScreenUpdating = False
    'Check rows 1 to 6
    For r = 1 To 6
        c = 1
        Do
            bFlag = Len(Cells(r, c)) = 0
            c = c + 1
        Loop While c <= 84 And bFlag
        Rows(r).Hidden = bFlag
    Next r
    
    'Check Columns A to CF
    For c = 1 To 84
        r = 1
        Do
            bFlag = Len(Cells(r, c)) = 0
            r = r + 1
        Loop While r <= 6 And bFlag
        Columns(c).Hidden = bFlag
    Next c
    
    Application.ScreenUpdating = True
    
End Sub


Sub UnHide_RowsCols()

    Application.ScreenUpdating = False
    Rows("1:6").Hidden = False
    Columns("A:CF").Hidden = False
    Application.ScreenUpdating = True
    
End Sub

 

[slowbob]

Thank you. (I was trying to exclude empty columns, btw.)

I was thinking I could do this without hide/unhide because any empty labels would always be consecutive through to the right-hand end of the strip.

But this works. Kludge is ok. All of my own macros are pretty ham-fisted. Thank you very much.