Using cell row # in a VB macro
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:D" & 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:D"&E1) .Select line, and also at the PrintArea line right after it. Thanks for any help... Betty |
Using cell row # in a VB macro
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 In article .com, "BettyFount" wrote: 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:D" & 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:D"&E1) .Select line, and also at the PrintArea line right after it. Thanks for any help... Betty |
Using cell row # in a VB macro
Thank-you so much! It works perfectly! And, you are right, your way is
much easier. Thanks again, Betty |
All times are GMT +1. The time now is 06:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com