Set CurrentRegion

C

CR

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
 
J

JLGWhiz

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.
 
C

CR

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?
 
J

JLGWhiz

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.
 
R

Rick Rothstein

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
 
J

JLGWhiz

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?
 
C

CR

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
 
R

Rick Rothstein

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.
 
J

JLGWhiz

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.
 
C

CR

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.
 
J

JLGWhiz

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.
 
Top