Set print area using variable
Posted by Ken on August 16, 2001 11:11 AM
I am trying to set a print area using a range with a fixed starting point and a variable for the end of the range. I found how to locate the last row and use it in my Sub. My variables contain the correct values after running, but I get a "type-mismatch error" when I make a reference to my variables using Cells, Columns or Rows in my Range statement.
Sub lastRow()
RealLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious) _
.Select
firstEntry = "B:63"
lastEntry = Selection.Address
Worksheets("Payroll").Range(Cells(firstEntry), _
Cells(lastEntry)).Select
End Sub
Posted by Damon Ostrander on August 16, 2001 8:58 PM
Hi Ken,
The reason you get the "Type Mismatch" is because the Cells property is an array that only accepts numerical integer arguments that are the row and column indices of the cell of interest. Your range statement should look like this:
Range(Cells(63,2),Selection).Select
which eliminates the need for both firstEntry and lastEntry. Note that the Range method when given two arguments requires range OBJECTS as arguments. Both Cells(63,2) and Selection yield a cell, which is a Range object.
Also I deliberately left off the Worksheets("Payroll") qualifier because I assume that this is already the active worksheet. If it is not the activeworksheet, adding the range qualifier will not be sufficient to make the code work, since it is not just the Range method that needs qualification, but also the Cells property. To make the code work in this case would require something like:
With Worksheets("Payroll")
.Range(.Cells(63,2),Selection).Select
End With
where the period in front of both Range and Cells
is essential.
Happy computing.
Damon
