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

DarkJester89

Board Regular
Joined
Nov 5, 2017
Messages
94
Office Version
  1. 2016
Platform
  1. Windows
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?

VBA Code:
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.

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

Thank you in advance.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
380
Office Version
  1. 2007
Platform
  1. Windows
Hello DarkJester89,
try this...

VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@EXCEL MAX, you do realise that xlCellTypeLastCell includes any cells with formatting but with no contents?
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
380
Office Version
  1. 2007
Platform
  1. Windows
Yes, I saw my mistake. Thank you.
I working on new solution...

VBA Code:
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
 
Last edited:
Solution

DarkJester89

Board Regular
Joined
Nov 5, 2017
Messages
94
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I got assistance to find this code, it works on rows, but not columns.
VBA Code:
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

Active Member
Joined
Nov 11, 2020
Messages
380
Office Version
  1. 2007
Platform
  1. Windows
Did you try...
VBA Code:
Sub Test3()

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

DarkJester89

Board Regular
Joined
Nov 5, 2017
Messages
94
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Did you try...
VBA Code:
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.
1611014471332.png
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
380
Office Version
  1. 2007
Platform
  1. Windows
I hope so you have choose right solution.
You know how it goes, code always can come to the wrong way.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,682
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top