| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet excluding rows with formulas
My worksheet has data in columns A to M. Data has been entered up to
Row 35, but there are "If" formulas in column D which extend down to Row 200. I have inserted a macro to select and print the area with data, which is up to Row 35. However, the print area selected by the macro is A1:M200. Apparently this is happening because the "If" formulas extend to Row 200. I would be thankful for any help I can get in getting the macro to select the required print range A1:M35 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing worksheet excluding rows with formulas
Show the code that determines the area to be printed and perhaps someone can
suggest something. Otherwise set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) rng.printout -- Regards, Tom Ogilvy "LennyG" wrote in message ... My worksheet has data in columns A to M. Data has been entered up to Row 35, but there are "If" formulas in column D which extend down to Row 200. I have inserted a macro to select and print the area with data, which is up to Row 35. However, the print area selected by the macro is A1:M200. Apparently this is happening because the "If" formulas extend to Row 200. I would be thankful for any help I can get in getting the macro to select the required print range A1:M35 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing worksheet excluding rows with formulas
Hi Tom,
The code which selects the print area includes as follows Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0,1),.End(xlToRight)).Columns.Count NDates = Range(.Offset(1,0),.End(xlDown)).Rows.Count Range(.Offset0,0).Offset(NDates,NHeads).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address Hoping That the above will help. Regards , "Tom Ogilvy" wrote: Show the code that determines the area to be printed and perhaps someone can suggest something. Otherwise set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) rng.printout -- Regards, Tom Ogilvy "LennyG" wrote in message ... My worksheet has data in columns A to M. Data has been entered up to Row 35, but there are "If" formulas in column D which extend down to Row 200. I have inserted a macro to select and print the area with data, which is up to Row 35. However, the print area selected by the macro is A1:M200. Apparently this is happening because the "If" formulas extend to Row 200. I would be thankful for any help I can get in getting the macro to select the required print range A1:M35 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing worksheet excluding rows with formulas
Your code gave me an error, but if I changed it to
Sub AA() Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0, 1), .End(xlToRight)).Columns.Count NDates = Range(.Offset(1, 0), .End(xlDown)).Rows.Count Range(.Offset(0, 0), .Offset(NDates, NHeads)).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address End Sub It worked for me and wasn't affected by formulas in column D. -- Regards, Tom Ogilvy "LennyG" wrote in message ... Hi Tom, The code which selects the print area includes as follows Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0,1),.End(xlToRight)).Columns.Count NDates = Range(.Offset(1,0),.End(xlDown)).Rows.Count Range(.Offset0,0).Offset(NDates,NHeads).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address Hoping That the above will help. Regards , "Tom Ogilvy" wrote: Show the code that determines the area to be printed and perhaps someone can suggest something. Otherwise set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) rng.printout -- Regards, Tom Ogilvy "LennyG" wrote in message ... My worksheet has data in columns A to M. Data has been entered up to Row 35, but there are "If" formulas in column D which extend down to Row 200. I have inserted a macro to select and print the area with data, which is up to Row 35. However, the print area selected by the macro is A1:M200. Apparently this is happening because the "If" formulas extend to Row 200. I would be thankful for any help I can get in getting the macro to select the required print range A1:M35 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing worksheet excluding rows with formulas
Hi Tom,
Thanks for your assistance. I also have in my code a line which gives the print area before I print This line follows the "End With" previously shown MsgBox"The entire data set is the range" & Range("EntireDataSet").Address, _ vbinformation, "Data Set Address." I continue to get the response "The entire data set is the range $A$1:$M$200" If I delete the formulas in Col D Rows 191:200, the print area adjusts to $A$1:$M$190, hence my suggestion that the formulas are the problem Thanks again for your support. Also , the previous lines sent to you were missing a second ")" after NHeads and I suspect that is why you got the error "Tom Ogilvy" wrote: Your code gave me an error, but if I changed it to Sub AA() Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0, 1), .End(xlToRight)).Columns.Count NDates = Range(.Offset(1, 0), .End(xlDown)).Rows.Count Range(.Offset(0, 0), .Offset(NDates, NHeads)).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address End Sub It worked for me and wasn't affected by formulas in column D. -- Regards, Tom Ogilvy "LennyG" wrote in message ... Hi Tom, The code which selects the print area includes as follows Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0,1),.End(xlToRight)).Columns.Count NDates = Range(.Offset(1,0),.End(xlDown)).Rows.Count Range(.Offset0,0).Offset(NDates,NHeads).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address Hoping That the above will help. Regards , "Tom Ogilvy" wrote: Show the code that determines the area to be printed and perhaps someone can suggest something. Otherwise set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) rng.printout -- Regards, Tom Ogilvy "LennyG" wrote in message ... My worksheet has data in columns A to M. Data has been entered up to Row 35, but there are "If" formulas in column D which extend down to Row 200. I have inserted a macro to select and print the area with data, which is up to Row 35. However, the print area selected by the macro is A1:M200. Apparently this is happening because the "If" formulas extend to Row 200. I would be thankful for any help I can get in getting the macro to select the required print range A1:M35 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing worksheet excluding rows with formulas
Range(.Offset0,0).Offset(NDates,NHeads).Name = "EntireDataSet"
Range(.Offset(0, 0), .Offset(NDates, NHeads)).Name = "EntireDataSet" There were a lot more problems than a missing paren. Range(.Offset(0,0).Offset(NDates,NHeads)).Name would refer to a single cell. If column A can be used to determine the last row to be printed, then my original suggestion would work: set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) ? rng.Address $A$1:$M$11 -- Regards, Tom Ogilvy "LennyG" wrote in message ... Hi Tom, Thanks for your assistance. I also have in my code a line which gives the print area before I print This line follows the "End With" previously shown MsgBox"The entire data set is the range" & Range("EntireDataSet").Address, _ vbinformation, "Data Set Address." I continue to get the response "The entire data set is the range $A$1:$M$200" If I delete the formulas in Col D Rows 191:200, the print area adjusts to $A$1:$M$190, hence my suggestion that the formulas are the problem Thanks again for your support. Also , the previous lines sent to you were missing a second ")" after NHeads and I suspect that is why you got the error "Tom Ogilvy" wrote: Your code gave me an error, but if I changed it to Sub AA() Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0, 1), .End(xlToRight)).Columns.Count NDates = Range(.Offset(1, 0), .End(xlDown)).Rows.Count Range(.Offset(0, 0), .Offset(NDates, NHeads)).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address End Sub It worked for me and wasn't affected by formulas in column D. -- Regards, Tom Ogilvy "LennyG" wrote in message ... Hi Tom, The code which selects the print area includes as follows Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0,1),.End(xlToRight)).Columns.Count NDates = Range(.Offset(1,0),.End(xlDown)).Rows.Count Range(.Offset0,0).Offset(NDates,NHeads).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address Hoping That the above will help. Regards , "Tom Ogilvy" wrote: Show the code that determines the area to be printed and perhaps someone can suggest something. Otherwise set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) rng.printout -- Regards, Tom Ogilvy "LennyG" wrote in message ... My worksheet has data in columns A to M. Data has been entered up to Row 35, but there are "If" formulas in column D which extend down to Row 200. I have inserted a macro to select and print the area with data, which is up to Row 35. However, the print area selected by the macro is A1:M200. Apparently this is happening because the "If" formulas extend to Row 200. I would be thankful for any help I can get in getting the macro to select the required print range A1:M35 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing worksheet excluding rows with formulas
Actually
Range(.Offset(0,0).Offset(NDates,NHeads)).Name would refer to a single cell. would be an error as well. Without the outer Range (which causes the error), it would be Sub CC() NDates = 35 NHeads = 13 With Range("A1") Debug.Print .Offset(0, 0).Offset(NDates, NHeads).Address End With End Sub Which would return N36. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Range(.Offset0,0).Offset(NDates,NHeads).Name = "EntireDataSet" Range(.Offset(0, 0), .Offset(NDates, NHeads)).Name = "EntireDataSet" There were a lot more problems than a missing paren. Range(.Offset(0,0).Offset(NDates,NHeads)).Name would refer to a single cell. If column A can be used to determine the last row to be printed, then my original suggestion would work: set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) ? rng.Address $A$1:$M$11 -- Regards, Tom Ogilvy "LennyG" wrote in message ... Hi Tom, Thanks for your assistance. I also have in my code a line which gives the print area before I print This line follows the "End With" previously shown MsgBox"The entire data set is the range" & Range("EntireDataSet").Address, _ vbinformation, "Data Set Address." I continue to get the response "The entire data set is the range $A$1:$M$200" If I delete the formulas in Col D Rows 191:200, the print area adjusts to $A$1:$M$190, hence my suggestion that the formulas are the problem Thanks again for your support. Also , the previous lines sent to you were missing a second ")" after NHeads and I suspect that is why you got the error "Tom Ogilvy" wrote: Your code gave me an error, but if I changed it to Sub AA() Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0, 1), .End(xlToRight)).Columns.Count NDates = Range(.Offset(1, 0), .End(xlDown)).Rows.Count Range(.Offset(0, 0), .Offset(NDates, NHeads)).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address End Sub It worked for me and wasn't affected by formulas in column D. -- Regards, Tom Ogilvy "LennyG" wrote in message ... Hi Tom, The code which selects the print area includes as follows Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0,1),.End(xlToRight)).Columns.Count NDates = Range(.Offset(1,0),.End(xlDown)).Rows.Count Range(.Offset0,0).Offset(NDates,NHeads).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address Hoping That the above will help. Regards , "Tom Ogilvy" wrote: Show the code that determines the area to be printed and perhaps someone can suggest something. Otherwise set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) rng.printout -- Regards, Tom Ogilvy "LennyG" wrote in message ... My worksheet has data in columns A to M. Data has been entered up to Row 35, but there are "If" formulas in column D which extend down to Row 200. I have inserted a macro to select and print the area with data, which is up to Row 35. However, the print area selected by the macro is A1:M200. Apparently this is happening because the "If" formulas extend to Row 200. I would be thankful for any help I can get in getting the macro to select the required print range A1:M35 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing worksheet excluding rows with formulas
Hi Tom,
I finally got it to work because of your suggestions. Thanks again. "Tom Ogilvy" wrote: Actually Range(.Offset(0,0).Offset(NDates,NHeads)).Name would refer to a single cell. would be an error as well. Without the outer Range (which causes the error), it would be Sub CC() NDates = 35 NHeads = 13 With Range("A1") Debug.Print .Offset(0, 0).Offset(NDates, NHeads).Address End With End Sub Which would return N36. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Range(.Offset0,0).Offset(NDates,NHeads).Name = "EntireDataSet" Range(.Offset(0, 0), .Offset(NDates, NHeads)).Name = "EntireDataSet" There were a lot more problems than a missing paren. Range(.Offset(0,0).Offset(NDates,NHeads)).Name would refer to a single cell. If column A can be used to determine the last row to be printed, then my original suggestion would work: set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) ? rng.Address $A$1:$M$11 -- Regards, Tom Ogilvy "LennyG" wrote in message ... Hi Tom, Thanks for your assistance. I also have in my code a line which gives the print area before I print This line follows the "End With" previously shown MsgBox"The entire data set is the range" & Range("EntireDataSet").Address, _ vbinformation, "Data Set Address." I continue to get the response "The entire data set is the range $A$1:$M$200" If I delete the formulas in Col D Rows 191:200, the print area adjusts to $A$1:$M$190, hence my suggestion that the formulas are the problem Thanks again for your support. Also , the previous lines sent to you were missing a second ")" after NHeads and I suspect that is why you got the error "Tom Ogilvy" wrote: Your code gave me an error, but if I changed it to Sub AA() Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0, 1), .End(xlToRight)).Columns.Count NDates = Range(.Offset(1, 0), .End(xlDown)).Rows.Count Range(.Offset(0, 0), .Offset(NDates, NHeads)).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address End Sub It worked for me and wasn't affected by formulas in column D. -- Regards, Tom Ogilvy "LennyG" wrote in message ... Hi Tom, The code which selects the print area includes as follows Dim NDates As Integer, NHeads As Integer With Range("A1") NHeads = Range(.Offset(0,1),.End(xlToRight)).Columns.Count NDates = Range(.Offset(1,0),.End(xlDown)).Rows.Count Range(.Offset0,0).Offset(NDates,NHeads).Name = "EntireDataSet" End With ActiveSheet.PageSetUp.PrintArea = Range("EntireDataSet").Address Hoping That the above will help. Regards , "Tom Ogilvy" wrote: Show the code that determines the area to be printed and perhaps someone can suggest something. Otherwise set rng = Range(Cells(rows.count,1).end(xlup), Range("M1")) rng.printout -- Regards, Tom Ogilvy "LennyG" wrote in message ... My worksheet has data in columns A to M. Data has been entered up to Row 35, but there are "If" formulas in column D which extend down to Row 200. I have inserted a macro to select and print the area with data, which is up to Row 35. However, the print area selected by the macro is A1:M200. Apparently this is happening because the "If" formulas extend to Row 200. I would be thankful for any help I can get in getting the macro to select the required print range A1:M35 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Printing a worksheet with and without formulas showing | Excel Discussion (Misc queries) | |||
| Printing formulas in worksheet | Excel Worksheet Functions | |||
| How can I print a worksheet excluding blank rows? | Excel Worksheet Functions | |||
| printing worksheet with hidden rows | Excel Worksheet Functions | |||
| Printing formulas on a worksheet | Excel Worksheet Functions | |||
Linear Mode
