Using cell row # in a VB macro

[BettyFount]

Hi all,
I have scanned your posts, and thought I had found the answer to my
problem. However, when I put this line into my macro, I get an error
message every time. Help!
I want to print a list that is the output of an "advanced filter"
routine... so the length of the list is different with each search. I
go to the bottom of the spreadsheet, then do {end} and {up}, then
calculate. I have a formula in cell A1 =cell("row") which calculates
the row # of the cell in the bottom of my list. I do a paste special
value of A1 to E1. I am trying to use the value in E1 in my macro to
determine the print area. (I am using Excel 2000 in Windows XP.)

Range("D4000").Select
Selection.End(xlUp).Select
Calculate
Range("A1").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("D1" & E1).Select
ActiveSheet.PageSetup.PrintArea = "$D$1:$D$" & E1
Selection.PrintOut Copies:=1, Collate:=True
End Sub

The macro errors out at the Range("D1"&E1) .Select line, and also at
the PrintArea line right after it.
Thanks for any help... Betty

 

[JE McGimpsey]

First, E1 is interpreted as a variable (which is why you should put
Option Explicit at the top of each module - the compiler would have told
you that the variable was undefined).

You meant

Range("E1").Value

instead.

However, I think you could save a lot of effort this way:

With ActiveSheet
.PageSetup.PrintArea = .Range(.Cells(1, "D"), _
.Cells(.Rows.Count, "D").End(xlUp)).Address
End With

 

[BettyFount]

Thank-you so much! It works perfectly! And, you are right, your way is
much easier.
Thanks again, Betty