erasing outside print area
 

hallo,
how can erase the empty cells, outside print area?

erasing outside print area
 

er

You can hide them but they cannot be deleted (XL2003) FormatRowHide or
FormatColumnHide (after selecting them first)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/


"er" wrote in message
...
hallo,
how can erase the empty cells, outside print area?

erasing outside print area
 

** Warning ** << This standard module worked for me, but who knows
if there is an underlying problem... anyway, give it a try..

Jim May

Sub Foo()
Dim temp As String
Dim mtemp As String
Dim rng As Range
temp = ActiveSheet.Names("print_area")
mtemp = Right(temp, Len(temp) - 1)
Set rng = ActiveSheet.UsedRange
For Each c In rng
If Application.Intersect(c, Range(mtemp)) Is Nothing Then
c.ClearContents
End If
Next
End Sub

"er" wrote:

hallo,
how can erase the empty cells, outside print area?

erasing outside print area
 

CRAP, After sending you the macro -- I re-read your request only to see that
you wanted to erase "EMPTY-CELLS" (DAAAA,,) - Where as I mistakenly took your
request to be erase "NONEMPTY-CELLS" outside the Print range.

Oh well, I learned something in the exercise, all was not wasted....

Have a good day

Jim

"er" wrote:

hallo,
how can erase the empty cells, outside print area?

This works for me.

erasing outside print area
 

Watch out for formulas!

mikerickson wrote:

This works for me.
Code:
--------------------
Dim pValues As Variant
With ActiveSheet
pValues = .Range(.PageSetup.PrintArea).Value
.Cells.ClearContents
.Range(.PageSetup.PrintArea).Value = pValues
End With
--------------------
er;513921 Wrote:
hallo,
how can erase the empty cells, outside print area?

--
mikerickson

--

Dave Peterson

True. It sould be .Formula not .Value

erasing outside print area
 

And watch out for multiple area print ranges.

mikerickson wrote:

True. It sould be .Formula not .Value

Dave Peterson;514023 Wrote:
Watch out for formulas!

mikerickson wrote:-

This works for me.
Code:
--------------------
Dim pValues As Variant
With ActiveSheet
pValues = .Range(.PageSetup.PrintArea).Value
.Cells.ClearContents
.Range(.PageSetup.PrintArea).Value = pValues
End With
--------------------
er;513921 Wrote:-
hallo,
how can erase the empty cells, outside print area?-

--
mikerickson-

--

Dave Peterson

--
mikerickson

--

Dave Peterson

I think these modifications will adresss those issues.
If your version is before Excel 2003, pm me and I'll send you a Split function.

erasing outside print area
 

Split was added in xl2k, so you should only have to modify the code if the
person is using xl97 (or earlier).

(Another approach would be to loop through the printarea range areas.)

mikerickson wrote:

I think these modifications will adresss those issues.

Code:
--------------------

Dim pValues() As Variant
Dim pAddress As Variant
Dim i As Long
pAddress = Split(ActiveSheet.PageSetup.PrintArea, ",")
ReDim pValues(UBound(pAddress))
For i = 0 To UBound(pAddress)
pValues(i) = Range(pAddress(i)).Formula
Next i
ActiveSheet.Cells.ClearContents
For i = 0 To UBound(pAddress)
Range(pAddress(i)).Formula = pValues(i)
Next i
--------------------

If your version is before Excel 2003, pm me and I'll send you a Split
function.
Dave Peterson;514206 Wrote:
And watch out for multiple area print ranges.

mikerickson wrote:-

True. It sould be .Formula not .Value

Dave Peterson;514023 Wrote:-
Watch out for formulas!

mikerickson wrote:--

This works for me.
Code:
--------------------
Dim pValues As Variant
With ActiveSheet
pValues = .Range(.PageSetup.PrintArea).Value
.Cells.ClearContents
.Range(.PageSetup.PrintArea).Value = pValues
End With
--------------------
er;513921 Wrote:-
hallo,
how can erase the empty cells, outside print area?-

--
mikerickson--

--

Dave Peterson-

--
mikerickson-

--

Dave Peterson

--
mikerickson

--

Dave Peterson

Split isn't supported on my Mac Excel 2004 either. This is what I use. It isn't a complete emulation, but it returns a 0-based array of strings, with an optional delimiter that defaults to " ".

erasing outside print area
 

MS shares another way to do split:

http://support.microsoft.com/default...b;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5

And if the string to be split isn't too long, Tom Ogilvy has shared this:

Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function




mikerickson wrote:

Split isn't supported on my Mac Excel 2004 either. This is what I use.
It isn't a complete emulation, but it returns a 0-based array of
strings, with an optional delimiter that defaults to " ".

Code:
--------------------
Function split(ByVal inputString As String, Optional delimiter As String)
Dim outRRay() As String
Dim point As Long, cutPoint As Long
If delimiter = vbNullString Then delimiter = " "
inputString = inputString & delimiter
ReDim outRRay(0 To ((Len(inputString) / 2) + 1))
point = 0
outRRay(0) = vbNullString

Do Until Len(inputString) = 0
cutPoint = InStr(inputString, delimiter)
outRRay(point) = Trim(Left(inputString, cutPoint - 1))
inputString = Mid(inputString, cutPoint + 1)
If outRRay(point) < vbNullString Then point = point + 1
Loop

If point = 0 Then point = 1
ReDim Preserve outRRay(0 To point - 1)
split = outRRay
End Function

--------------------

Dave Peterson;514432 Wrote:
Split was added in xl2k, so you should only have to modify the code if
the
person is using xl97 (or earlier).

(Another approach would be to loop through the printarea range areas.)

mikerickson wrote:-

I think these modifications will adresss those issues.

Code:
--------------------

Dim pValues() As Variant
Dim pAddress As Variant
Dim i As Long
pAddress = Split(ActiveSheet.PageSetup.PrintArea, ",")
ReDim pValues(UBound(pAddress))
For i = 0 To UBound(pAddress)
pValues(i) = Range(pAddress(i)).Formula
Next i
ActiveSheet.Cells.ClearContents
For i = 0 To UBound(pAddress)
Range(pAddress(i)).Formula = pValues(i)
Next i
--------------------

If your version is before Excel 2003, pm me and I'll send you a
Split
function.
Dave Peterson;514206 Wrote:-
And watch out for multiple area print ranges.

mikerickson wrote:--

True. It sould be .Formula not .Value

Dave Peterson;514023 Wrote:-
Watch out for formulas!

mikerickson wrote:--

This works for me.
Code:
--------------------
Dim pValues As Variant
With ActiveSheet
pValues = .Range(.PageSetup.PrintArea).Value
.Cells.ClearContents
.Range(.PageSetup.PrintArea).Value = pValues
End With
--------------------
er;513921 Wrote:-
hallo,
how can erase the empty cells, outside print area?-

--
mikerickson--

--

Dave Peterson-

--
mikerickson--

--

Dave Peterson-

--
mikerickson-

--

Dave Peterson

--
mikerickson

--

Dave Peterson