Setting print area in a macro

[pkenard]

I am new to writting macros, I have tried two different methods to set the range for the PrintArea in a macro, neither work, See below

Sub CountRows()
Dim RowCount As Integer
Dim FinalCell As String
Dim row1 As Integer
Dim colstart As String
Dim colend As String
Dim RowsE As Double

row1 = 8 'Beginning ROW number

colstart = "$A$8" 'PrintArea Starting location

colend = "$J$" 'PrintArea ending column

'Calculate the number of rows in the spreadsheet

Dim NumberOfRows As Long
With Sheet1
NumberOfRows = .Cells(.Rows.Count, 1).End(xlUp).Row
NumberOfRows = .Range("A" & .Rows.Count).End(xlUp).Row
MsgBox NumberOfRows

End With

FinalCell = colend & NumberOfRows 'PrintArea ending cell


MsgBox FinalCell


'Worksheets("Parts List").PageSetup.PrintArea = "$A$8:$J$26" ' THIS WORKS
'Worksheets("Parts List").PageSetup.PrintArea = "$A$8:FinalCell"

'Worksheets("Parts List").PageSetup.PrintArea = "Cells(8,1):Cells("FinalCell",10)"
'Worksheets("Parts List").PageSetup.PrintArea = "Cells(8,1):Cells(26,10)" 'THIS WORKS

End Sub


What am I doing wrong?

Thanks

[Jack in the UK]

Does the code fail? If so at what line does the code fail...

BTW You need to add code tags to Your posts or the Mods will chase You !!

[pkenard]

If the line below is un commented then

Worksheets("Parts List").PageSetup.PrintArea = "$A$8:FinalCell"

I get "Run-time error '1004': The text you entered is not a valid reference or defin

BTW what is a "code Tag"?

[Jack in the UK]

You will as FinalCEll reports $j$1 not J = 10 and 10 is what you require

so the code needs to read

$A$8:$J:$1

trouble is you have this $A$:$J$1 & (1) ' rem CRASH

so you need this $A$:$J & 1

Does that make sence?

[dangelor]

Worksheets("Parts List").PageSetup.PrintArea = "$A$8:FinalCell"

Try changing the above line to:

Please Login or Register  to view this content.