Solved: set print area problem

[lilstevie]

This is killing me, I'm sure that it is something stupid that I've overlooked.

I have a userform that runs a filter on a ws (combined data) and then copies the filtered data to another ws(comments) and 'hopefully' prints up the updated comments(ws). The problem is that the print area of the comments ws will not set for me. Sometimes the code works perfectly sometimes it only finds the top 4 rows (or header section). Please help!

Here is the code:
[VBA]Private Sub cmdCreateReport_Click()
Dim i As Long, msg As String, Check As String
Dim LR As Long, LR2 As Long, rng2 As Range
Dim lastrow2 As Long
Dim endrange As String

'check for blanks
If Me.tbxSTARTDATE.Value = "SELECT DATE" Then
MsgBox "Please select the report START date!", vbOKOnly, "Enter start date"
Me.cmdCreateReport.Enabled = False
Me.tbxSTARTDATE.SetFocus
Exit Sub
End If
If Me.tbxENDDATE.Enabled = True And Me.tbxENDDATE.Value = "SELECT DATE" Then
MsgBox "Please select the report end date!", vbOKOnly, "Enter end date"
Me.cmdCreateReport.Enabled = False
Me.tbxENDDATE.SetFocus
Exit Sub
End If
If Me.lstbxEmployeeName.ListIndex = -1 Then
MsgBox "Please select employee name!", vbOKOnly, "Select Employee"
Me.cmdCreateReport.Enabled = False
Me.lstbxEmployeeName.SetFocus
Exit Sub
Else
'Ask the user if they are happy with their selection(s)
Check = MsgBox("You selected:" & vbNewLine & Me.lstbxEmployeeName.Value & vbNewLine & _
"Are you happy with your selection?", _
vbYesNo + vbInformation, "Please confirm")
End If

If Check = vbYes Then
rptempname = Me.lstbxEmployeeName.Value
Unload Me
With Sheet8
.AutoFilterMode = False
With .Range("A5:k6")
.AutoFilter
.AutoFilter Field:=4, Criteria1:=">=" & rptstartdate, Operator:=xlAnd, Criteria2:="<=" & rptenddate
.AutoFilter Field:=1, Criteria1:="=" & rptempname
.AutoFilter Field:=11, Criteria1:="<>"
End With
End With

Sheet8.Range("b1").Value = rptempname
Sheet8.Range("b2").Value = rptstartdate
Sheet8.Range("b3").Value = rptenddate
Sheet8.Range("k1").Value = "Comments for: " & rptempname
Sheet6.Range("a2").Value = rptempname
Sheet6.Range("a3").Value = rptstartdate & " to " & rptenddate

If Sheet8.Range("B4").Value = 0 Then
MsgBox "No comments for selected period" & vbNewLine & vbNewLine & " Please refine search criteria"
frmComments.Show
Exit Sub

Else
LR = Sheet8.Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheet6.Range("a" & Rows.Count).End(xlUp).Row
Sheet6.Range("A6:B" & LR2).Value = ""
With Sheet8.Range("d6:d" & LR)
.SpecialCells(xlCellTypeVisible).Copy
End With
Sheet6.Range("A6:a" & LR).PasteSpecial Paste:=xlPasteValues
With Sheet8.Range("k6:k" & LR)
.SpecialCells(xlCellTypeVisible).Copy
End With
Sheet6.Range("b6:b" & LR).PasteSpecial Paste:=xlPasteValues
End If


lastrow2 = Sheet6.Range("A" & Rows.Count).End(xlUp).Row
With Sheet6.Range("A6:B" & lastrow2)
.wraptext = True
.VerticalAlignment = xlTop
End With
With Sheet6.Range("$A$10000:B" & lastrow2 + 1)
.ClearContents
End With

Application.ScreenUpdating = True

endrange = Cells(Rows.Count, 2).End(xlUp).Address

Sheet6.PageSetup.PrintArea = "$A$1:" & endrange
Sheet6.PageSetup.PrintArea = "$A$1:" & endrange
Sheet6.PrintOut Copies:=1
protectall

End If
End Sub[/VBA]

[lilstevie]

Here is the file, It is the Comments userform

[lucas]

try getting the used range instead.....see if that works any better:
[vba]With Sheets("Sheet6")
.PageSetup.PrintArea = Sheet6.UsedRange
End With[/vba]

[lilstevie]

Nope, error "1004"

unable to set the print area of the page setup.

[david000]

[VBA]
With Sheet6.Range("A6:B" & lastrow2) '< Range("a5") is blank!
[/VBA]
[VBA]
lastrow2 = Sheet6.Range("A" & Rows.Count).End(xlUp).Row
[/VBA]
you are only going "end(xlup)" to row 4.

[lilstevie]

Hi David,

I thought the xlup looks for the first used cell from the bottom up and xldown looks from the top down? the blank 5th row should not matter..?

[david000]

Try it by hand and you'll see what I mean.

In your code your going to Range("a6:b & lastrow")

So, manually go range a6 and press Ctrl + Shift + Up Arrow.

If Range("a5") is blank you'll stop there and the the print area will be the base of row 4.

[david000]

Fix these lines in your code to this
I was haveing truble running it cause I don't have the calendar controls and stuff on my home PC, so I had to step though it a peice at a time until I found what looked odd...but this Im pretty sure is the bug.



[vba]
Dim endrange as long
endrange = Sheet6.Cells(Rows.Count, 2).End(xlUp).Row
Sheet6.PageSetup.PrintArea = Range("a1:b & endrange).Address
[/vba]
I'm sure this is it this time.

AND get rid of the duplicate line at the end too!!!!!!!!!!!!!!!!!!

[lilstevie]

Thanks for looking, I'll give your code a try when I get home from work. My work PC doesn't allow Excel. I'll also try filling in row 5 and seeing if that works.

Steve

[lilstevie]

Think you nailed it:

Had to change the endrange back to string and then it worked great.

Why did it need the .Address added to it ?

[david000]

Think of the PrintArea like you would think of a Range Name. It's an address that points to a Range not a range object unto itself.

In the help file they show a "hard-coded" address and the CurrentRegion.Address which is what lucas was suggesting in his post except he didn't throw on the .Address but he was right anyway as well.


Excel Help starts here:
Excel Developer Reference
PageSetup.PrintArea Property
Returns or sets the range to be printed, as a string using A1-style references in the language of the macro. Read/write String.expression.PrintArea
expression A variable that represents a PageSetup object.

Remarks


Set this property to False or to the empty string ("") to set the print area to the entire sheet.

This property applies only to worksheet pages.


Example


This example sets the print area to cells A1:C5 on Sheet1.

Visual Basic for Applications
Worksheets("Sheet1").PageSetup.PrintArea = "$A$1:$C$5"

This example sets the print area to the current region on Sheet1. Note that you use the Address property to return an A1-style address.

Visual Basic for Applications
Worksheets("Sheet1").Activate
ActiveSheet.PageSetup.PrintArea = _
ActiveCell.CurrentRegion.Address