Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
I'm setting a print area using CurrentRegion.
Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
Not if you are going to use the CurrentRegion.Address as the print area
parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
All of the cells have formulas. Say, A1:AZ100 At times the visible data only
goes to columns AW or AX or AY. There are no blank cells in the rest of the area . If CurrentRegion won't work is there another way? "JLGWhiz" wrote in message ... Not if you are going to use the CurrentRegion.Address as the print area parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
Without seeing what the current region looks like, it would be presumptious
to offer any code. What you would have to do is move the visible data to a location on the sheet (or a sheet) where it can be grouped or arranged as you would like to see it printed. I am not so sure it is worth the effort, but then I have no idea what your requirements are. There is no quick and easy solution. However, If the data is truncated by column or by row you might be able to identify the visible range by looking for the last column or row with a sum greater than zero, or CountA is greater than zero, depending on what the visible data type is. "CR" wrote in message ... All of the cells have formulas. Say, A1:AZ100 At times the visible data only goes to columns AW or AX or AY. There are no blank cells in the rest of the area . If CurrentRegion won't work is there another way? "JLGWhiz" wrote in message ... Not if you are going to use the CurrentRegion.Address as the print area parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
If I you are saying that your data has no internal blank cells, and that
your data starts in A1, then you can use this macro to set the print area for only the cells displaying a value... Sub SetPrintArea() Dim LastRow As Long, LastCol As Long LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row LastCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column ActiveSheet.PageSetup.PrintArea = Range("A1"). _ Resize(LastRow, LastCol).Address End Sub -- Rick (MVP - Excel) "CR" wrote in message ... All of the cells have formulas. Say, A1:AZ100 At times the visible data only goes to columns AW or AX or AY. There are no blank cells in the rest of the area . If CurrentRegion won't work is there another way? "JLGWhiz" wrote in message ... Not if you are going to use the CurrentRegion.Address as the print area parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
Rick, I interpreted the parameters as a range which has formulas, some of
which will produce a value of "", empty string. The OP does not want the empty string cells to be included in the print area. I believe your lastrow and lastcolumn criteria would include those cells with empty string. Or am I reading it wrong? "Rick Rothstein" wrote in message ... If I you are saying that your data has no internal blank cells, and that your data starts in A1, then you can use this macro to set the print area for only the cells displaying a value... Sub SetPrintArea() Dim LastRow As Long, LastCol As Long LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row LastCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column ActiveSheet.PageSetup.PrintArea = Range("A1"). _ Resize(LastRow, LastCol).Address End Sub -- Rick (MVP - Excel) "CR" wrote in message ... All of the cells have formulas. Say, A1:AZ100 At times the visible data only goes to columns AW or AX or AY. There are no blank cells in the rest of the area . If CurrentRegion won't work is there another way? "JLGWhiz" wrote in message ... Not if you are going to use the CurrentRegion.Address as the print area parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
Rick, That didn't quite get it either. It set the print area to all cells
that had border formatting also. I started playing with a smaller version of the sheet to try a work-around, and I got it to work. It finds if there is data in the last columns that are sometimes blank in the none active worksheet. If not it cuts the formulas out of the corresponding column in the active sheet, sets the print area and then pastes them back. It is always the columns on the right-hand side so the CurrentRegion works. But I have a question I hope someone can answer for my future reference. The below code works and I will modify it to include the other columns, but the 'commented out code would not work and I wondered why. Private Sub Cmd_SelectPrint_Click() Dim WS As Object Dim Q As Range Dim W As Range Set WS = ThisWorkbook.Sheets("Trucks") Set Q = Range("Q1:Q65") Set W = Range("W1:W65") If WS.Range("O2").Value = ("") Then Q.Cut ' W.Paste ' W.PasteSpecial paste:=xlPasteAll W.Select ActiveSheet.paste ActiveSheet.Range("C1").CurrentRegion.Select ActiveSheet.PageSetup.PrintArea = Selection.Address ' W.Cut ' Q.Select ' ActiveSheet.paste ActiveSheet.Range("W1:W65").Select Selection.Cut Range("Q1:Q65").Select ActiveSheet.paste End If End Sub "Rick Rothstein" wrote in message ... If I you are saying that your data has no internal blank cells, and that your data starts in A1, then you can use this macro to set the print area for only the cells displaying a value... Sub SetPrintArea() Dim LastRow As Long, LastCol As Long LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row LastCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column ActiveSheet.PageSetup.PrintArea = Range("A1"). _ Resize(LastRow, LastCol).Address End Sub -- Rick (MVP - Excel) "CR" wrote in message ... All of the cells have formulas. Say, A1:AZ100 At times the visible data only goes to columns AW or AX or AY. There are no blank cells in the rest of the area . If CurrentRegion won't work is there another way? "JLGWhiz" wrote in message ... Not if you are going to use the CurrentRegion.Address as the print area parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
The LastRow and LastCol statements I used will find the last non-empty cell
"value-wise" in a row and the last non-empty cell "value-wise" in a column even if there are formulas after them which are displaying the empty string (""). The reason these statements skip the empty formulas is because I used LookIn:=xlValues... if I had used LookIn:=xlFormulas instead, then they would have found the last filled row and column having something in them even if that were a formula displaying the empty string. -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... Rick, I interpreted the parameters as a range which has formulas, some of which will produce a value of "", empty string. The OP does not want the empty string cells to be included in the print area. I believe your lastrow and lastcolumn criteria would include those cells with empty string. Or am I reading it wrong? "Rick Rothstein" wrote in message ... If I you are saying that your data has no internal blank cells, and that your data starts in A1, then you can use this macro to set the print area for only the cells displaying a value... Sub SetPrintArea() Dim LastRow As Long, LastCol As Long LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row LastCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column ActiveSheet.PageSetup.PrintArea = Range("A1"). _ Resize(LastRow, LastCol).Address End Sub -- Rick (MVP - Excel) "CR" wrote in message ... All of the cells have formulas. Say, A1:AZ100 At times the visible data only goes to columns AW or AX or AY. There are no blank cells in the rest of the area . If CurrentRegion won't work is there another way? "JLGWhiz" wrote in message ... Not if you are going to use the CurrentRegion.Address as the print area parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
The problem appears to be that Excel does not want to allow the paste
special to work with the Cut mode. It works fine with the Copy mode. I took a brief look for some MicroSoft bulletin on it, but did not see anything. I am sure there must be some literature on it somewhere. In the meantime, when you intend to paste all, you could use syntax as follows: Q.Cut W That little bit does the whole operation in one fell swoop. "CR" wrote in message ... Rick, That didn't quite get it either. It set the print area to all cells that had border formatting also. I started playing with a smaller version of the sheet to try a work-around, and I got it to work. It finds if there is data in the last columns that are sometimes blank in the none active worksheet. If not it cuts the formulas out of the corresponding column in the active sheet, sets the print area and then pastes them back. It is always the columns on the right-hand side so the CurrentRegion works. But I have a question I hope someone can answer for my future reference. The below code works and I will modify it to include the other columns, but the 'commented out code would not work and I wondered why. Private Sub Cmd_SelectPrint_Click() Dim WS As Object Dim Q As Range Dim W As Range Set WS = ThisWorkbook.Sheets("Trucks") Set Q = Range("Q1:Q65") Set W = Range("W1:W65") If WS.Range("O2").Value = ("") Then Q.Cut ' W.Paste ' W.PasteSpecial paste:=xlPasteAll W.Select ActiveSheet.paste ActiveSheet.Range("C1").CurrentRegion.Select ActiveSheet.PageSetup.PrintArea = Selection.Address ' W.Cut ' Q.Select ' ActiveSheet.paste ActiveSheet.Range("W1:W65").Select Selection.Cut Range("Q1:Q65").Select ActiveSheet.paste End If End Sub "Rick Rothstein" wrote in message ... If I you are saying that your data has no internal blank cells, and that your data starts in A1, then you can use this macro to set the print area for only the cells displaying a value... Sub SetPrintArea() Dim LastRow As Long, LastCol As Long LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row LastCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column ActiveSheet.PageSetup.PrintArea = Range("A1"). _ Resize(LastRow, LastCol).Address End Sub -- Rick (MVP - Excel) "CR" wrote in message ... All of the cells have formulas. Say, A1:AZ100 At times the visible data only goes to columns AW or AX or AY. There are no blank cells in the rest of the area . If CurrentRegion won't work is there another way? "JLGWhiz" wrote in message ... Not if you are going to use the CurrentRegion.Address as the print area parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
Thanks for the Q.Cut W
The code that was commented was actually two different trys at it. Once with just W.Paste and then with the Special , neither would work. Q.Cut ' W.Paste ' W.PasteSpecial paste:=xlPasteAll W.Select ActiveSheet.paste The second part is what really puzzled me. It's exactly the same as what worked in the first one. I think it has to do with setting the print area. I tried this first with a variable x, to hold and it wouldn't read the x after the code to set the print. ' W.Cut ' Q.Select ' ActiveSheet.paste Anyway Thanks to you and Rick, someday I may actually understand this stuff. "JLGWhiz" wrote in message ... The problem appears to be that Excel does not want to allow the paste special to work with the Cut mode. It works fine with the Copy mode. I took a brief look for some MicroSoft bulletin on it, but did not see anything. I am sure there must be some literature on it somewhere. In the meantime, when you intend to paste all, you could use syntax as follows: Q.Cut W That little bit does the whole operation in one fell swoop. "CR" wrote in message ... Rick, That didn't quite get it either. It set the print area to all cells that had border formatting also. I started playing with a smaller version of the sheet to try a work-around, and I got it to work. It finds if there is data in the last columns that are sometimes blank in the none active worksheet. If not it cuts the formulas out of the corresponding column in the active sheet, sets the print area and then pastes them back. It is always the columns on the right-hand side so the CurrentRegion works. But I have a question I hope someone can answer for my future reference. The below code works and I will modify it to include the other columns, but the 'commented out code would not work and I wondered why. Private Sub Cmd_SelectPrint_Click() Dim WS As Object Dim Q As Range Dim W As Range Set WS = ThisWorkbook.Sheets("Trucks") Set Q = Range("Q1:Q65") Set W = Range("W1:W65") If WS.Range("O2").Value = ("") Then Q.Cut ' W.Paste ' W.PasteSpecial paste:=xlPasteAll W.Select ActiveSheet.paste ActiveSheet.Range("C1").CurrentRegion.Select ActiveSheet.PageSetup.PrintArea = Selection.Address ' W.Cut ' Q.Select ' ActiveSheet.paste ActiveSheet.Range("W1:W65").Select Selection.Cut Range("Q1:Q65").Select ActiveSheet.paste End If End Sub "Rick Rothstein" wrote in message ... If I you are saying that your data has no internal blank cells, and that your data starts in A1, then you can use this macro to set the print area for only the cells displaying a value... Sub SetPrintArea() Dim LastRow As Long, LastCol As Long LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row LastCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column ActiveSheet.PageSetup.PrintArea = Range("A1"). _ Resize(LastRow, LastCol).Address End Sub -- Rick (MVP - Excel) "CR" wrote in message ... All of the cells have formulas. Say, A1:AZ100 At times the visible data only goes to columns AW or AX or AY. There are no blank cells in the rest of the area . If CurrentRegion won't work is there another way? "JLGWhiz" wrote in message ... Not if you are going to use the CurrentRegion.Address as the print area parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CurrentRegion
One other thing about using the Q.Cut W method. The receiving range has to
be equal to or greater than the source range. Otherwise you get a message that it cannot execute the command. "CR" wrote in message ... Thanks for the Q.Cut W The code that was commented was actually two different trys at it. Once with just W.Paste and then with the Special , neither would work. Q.Cut ' W.Paste ' W.PasteSpecial paste:=xlPasteAll W.Select ActiveSheet.paste The second part is what really puzzled me. It's exactly the same as what worked in the first one. I think it has to do with setting the print area. I tried this first with a variable x, to hold and it wouldn't read the x after the code to set the print. ' W.Cut ' Q.Select ' ActiveSheet.paste Anyway Thanks to you and Rick, someday I may actually understand this stuff. "JLGWhiz" wrote in message ... The problem appears to be that Excel does not want to allow the paste special to work with the Cut mode. It works fine with the Copy mode. I took a brief look for some MicroSoft bulletin on it, but did not see anything. I am sure there must be some literature on it somewhere. In the meantime, when you intend to paste all, you could use syntax as follows: Q.Cut W That little bit does the whole operation in one fell swoop. "CR" wrote in message ... Rick, That didn't quite get it either. It set the print area to all cells that had border formatting also. I started playing with a smaller version of the sheet to try a work-around, and I got it to work. It finds if there is data in the last columns that are sometimes blank in the none active worksheet. If not it cuts the formulas out of the corresponding column in the active sheet, sets the print area and then pastes them back. It is always the columns on the right-hand side so the CurrentRegion works. But I have a question I hope someone can answer for my future reference. The below code works and I will modify it to include the other columns, but the 'commented out code would not work and I wondered why. Private Sub Cmd_SelectPrint_Click() Dim WS As Object Dim Q As Range Dim W As Range Set WS = ThisWorkbook.Sheets("Trucks") Set Q = Range("Q1:Q65") Set W = Range("W1:W65") If WS.Range("O2").Value = ("") Then Q.Cut ' W.Paste ' W.PasteSpecial paste:=xlPasteAll W.Select ActiveSheet.paste ActiveSheet.Range("C1").CurrentRegion.Select ActiveSheet.PageSetup.PrintArea = Selection.Address ' W.Cut ' Q.Select ' ActiveSheet.paste ActiveSheet.Range("W1:W65").Select Selection.Cut Range("Q1:Q65").Select ActiveSheet.paste End If End Sub "Rick Rothstein" wrote in message ... If I you are saying that your data has no internal blank cells, and that your data starts in A1, then you can use this macro to set the print area for only the cells displaying a value... Sub SetPrintArea() Dim LastRow As Long, LastCol As Long LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row LastCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column ActiveSheet.PageSetup.PrintArea = Range("A1"). _ Resize(LastRow, LastCol).Address End Sub -- Rick (MVP - Excel) "CR" wrote in message ... All of the cells have formulas. Say, A1:AZ100 At times the visible data only goes to columns AW or AX or AY. There are no blank cells in the rest of the area . If CurrentRegion won't work is there another way? "JLGWhiz" wrote in message ... Not if you are going to use the CurrentRegion.Address as the print area parameters. Excel takes the entire range from top left to bottom right cells as the print range. You would have to do a little manipulating if you only want to pring the visible data and I am not sure I have the expertise to tell you exactly how to do it. "CR" wrote in message ... I'm setting a print area using CurrentRegion. Is there a way to have the current region ignore cells that only have a formula in them? Thanks CR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CurrentRegion.Address | Excel Programming | |||
currentregion | Excel Discussion (Misc queries) | |||
CurrentRegion less one Row | Excel Programming | |||
CurrentRegion | Excel Programming | |||
CurrentRegion Selecting | Excel Programming |