| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
erasing outside print area
hallo,
how can erase the empty cells, outside print area? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
|
|||
|
|||
|
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
Quote:
|
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
True. It sould be .Formula not .Value
Quote:
|
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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
Quote:
|
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
|
|||
|
|||
|
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
Quote:
|
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| set area in excel not giving me option to set print area? | Excel Discussion (Misc queries) | |||
| print area across the freeze panes area | Excel Worksheet Functions | |||
| Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
| Active cell counting in particular print page (one sheet having different print area) | Excel Worksheet Functions | |||
| How do you turn off a print area for a page? (no print area) | Excel Discussion (Misc queries) | |||
Linear Mode
