Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'IF' Macro to insert cell contents to alternate cell if cell not e | Excel Worksheet Functions | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
If cell is empty then run macro otherwise skip this macro | Excel Programming | |||
macro to run a separate macro dependent on value in cell | Excel Programming | |||
Question: Cell formula or macro to write result of one cell to another cell | Excel Programming |