How to set print area up to last visible row/column?

[DarkJester89]

Worksheet has 100 rows and about 150 columns. At any given time, only 20/40 of these will be visible.

CTRL+ UP for rows = 100 (row) Ctrl+LEFT = CS (column)

Is there a way to VBA set print area for visible rows/columns?

Private Sub CommandButton2_Click()    
Worksheets("Sheet1").PageSetup.PrintArea = Range("B:H").Address
End Sub

This is an adjacent set print area that works. New Range would be A1: to last row and column that has visible data.

I've attempted dynamic name range for print area (couldn't get that to work) and I found this code but I wouldn't work in the = range in the code above.

ActiveSheet.PageSetup.PrintArea = Range("C3:I" & lastrow).Rows.SpecialCells(xlCellTypeVisible).Address

Thank you in advance.

 

[EXCEL MAX]

Hello DarkJester89,
try this...

Sub Test()
   
    Dim varNRows As Long
    Dim varNColumns As Integer
   
    varNColumns = Range("1:1").SpecialCells(xlCellTypeLastCell).Column
    varNRows = Range("A:A").SpecialCells(xlCellTypeLastCell).Row
    ActiveSheet.PageSetup.PrintArea = _
    Range(Range("A1"), Cells(varNRows, varNColumns)).Address

End Sub

 

[MARK858]

@EXCEL MAX, you do realise that xlCellTypeLastCell includes any cells with formatting but with no contents?

 

[EXCEL MAX]

Yes, I saw my mistake. Thank you.
I working on new solution...

Sub Test2()
   
    Dim varNRows As Long
    Dim varNColumns As Integer
   
    varNColumns = Rows(1).Find("*", Cells(1, Columns.Count), , , , 2).Column
    varNRows = Columns(1).Find("*", Cells(Rows.Count, 1), , , , 2).Row
    ActiveSheet.PageSetup.PrintArea = _
    Range("A1", Cells(varNRows, varNColumns)).Address

End Sub

 

[DarkJester89]

I got assistance to find this code, it works on rows, but not columns.

Public Sub PrintRange()
    
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    
    Dim lastRow As Long
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
    
    Dim lastColumn As Long
    lastColumn = targetSheet.Cells(1, targetSheet.Columns.Count).End(xlToLeft).Column
    
    Dim printRange As Range
    Set printRange = targetSheet.Range("A1", targetSheet.Cells(lastRow, lastColumn))
    
    targetSheet.PageSetup.PrintArea = printRange.address
    
End Sub

 

[EXCEL MAX]

Did you try...

Sub Test3()

    targetSheet.PageSetup.PrintArea = targetSheet.UsedRange.Address
    
End Sub

 

[DarkJester89]

Did you try...

Sub Test3()

    targetSheet.PageSetup.PrintArea = targetSheet.UsedRange.Address
   
End Sub

Some of these op-tests really make me chuckle. this is after trying it. I don't think it worked.

 

[DarkJester89]

@excelmax Thank you, yours worked great!

 

[EXCEL MAX]

I hope so you have choose right solution.
You know how it goes, code always can come to the wrong way.