setting print range

[mike allen]

I know how to set a print range w/ the columns set and the rows varying, but
what about the other way around where I don't know the number of columns
every time?

this works for varying rows:
varyingrows = 8
Sheets("testpage").PageSetup.PrintArea = "a1:j" & varyingrows

I tried this for varying columns with no luck:
varyingcolumns=10 'will change w/ every run
Sheets("testpage").PageSetup.PrintArea =
range(cells(1,1),cells(8,varyingcolumns)

any thoughts? thanks, mike allen

 

[Incidental]

Hi Mike

you could try something like this to find the last used row and column
then use that info to set your print range.

Option Explicit
Dim VaryingRows As Integer
Dim VaryingColumns As String
Dim MyLen As Long

Private Sub CommandButton1_Click()
VaryingRows = [a1].End(xlDown).Row 'Find last used row in Column A
VaryingColumns = [a1].End(xlToRight).Address 'Find last used column in
Row 1
MyLen = Len(VaryingColumns) 'Check if column is single letter or
double
If MyLen = 5 Then
VaryingColumns = Mid(VaryingColumns, 2, 2) 'Take the column letter
from the string
Else
VaryingColumns = Mid(VaryingColumns, 2, 1)
End If
'Set your print area
Sheets("testpage").PageSetup.PrintArea = "a1:" & VaryingColumns &
VaryingRows
End Sub

Hope this is of some help to you

S

 

[Incidental]

Hi Mike

Sorry if this post appears twice i sent it through already but it
hasn't appeared.

You could try something like the code below that finds the last used
column and row referencing from the cell A1 then sets the print area
using this info.

Option Explicit
Dim VaryingRows As Integer
Dim VaryingColumns As String
Dim MyLen As Long

Private Sub CommandButton1_Click()
VaryingRows = [A1].End(xlDown).Row 'Find last used row in Column A
VaryingColumns = [A1].End(xlToRight).Address 'Find last used column in
Row 1
MyLen = Len(VaryingColumns) 'Check if column is single letter or
double
If MyLen = 5 Then
VaryingColumns = Mid(VaryingColumns, 2, 2) 'Take the column letter
from the string
Else
VaryingColumns = Mid(VaryingColumns, 2, 1)
End If
'Set your print area
Sheets("Sheet1").PageSetup.PrintArea = "A1:" & VaryingColumns &
VaryingRows
End Sub

I hope this is of some help to you.

S

 

[mike allen]

i got it! it works just fine. the main thing i left out was the ...Address
suffix. thank you, mike allen