erasing outside print area

  • Thread starter Thread starter Guest
  • Start date Start date
** 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
 
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
 
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
--------------------
 
Watch out for formulas!
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
Click to expand...
 
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
 
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.)
 
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

--------------------
 
MS shares another way to do split:

http://support.microsoft.com/default.aspx?scid=kb;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
 
Back
Top