Hi Tom,
I think you would want to delete the entire row and entire column of
IV65536 to reset the used range or your file size will grow tremendously
I found no need to delete either the row or column as I make no physical
entry in this cell. In testing, saving the file after running the procedure
was sufficient to restore the initial used range and, to within plus/minus
1%, the initial file size.
which begs the question of why worry about these unused cells anyway
I, myself, am not concerned by these cells and I endeavoured to convey this
in my final paragraph:
That said, discussion as to the merits or demerits of one approach over
another is moot to the extent that the solution represents the resolution
of
a problem that I would not expect to encounter.
As regards your final point:
If the Normal Style is set to Tahoma, then any "new" cell will be
formatted with Tahoma.
My understanding of the OP's requirement is that only blank cells be
formatted in Tahoma. I believe that changing the Normal Style acts on empty
and populated cells alike.
---
Regards,
Norman
"Tom Ogilvy" wrote in message
...
I think you would want to delete the entire row and entire column of
IV65536 to reset the used range or your file size will grow tremendously -
which begs the question of why worry about these unused cells anyway. If
the Normal Style is set to Tahoma, then any "new" cell will be formatted
with Tahoma.
--
Regards,
Tom Ogilvy
"Norman Jones" wrote in message
...
Hi Swatsp0p,
I have no problem with the manual approach except that it is very
substantially slower than the programmatic solution. Using my procedure,
I
was able to process a five sheet workbook in approximately the same time
as
I was able to process a single sheet manually. This, of course,
presupposes
the existence of the code. <g
Incidentally, you could remove a redundant step from your suggestion:
instead of making an entry in cell IV65536 and later deleting the entry,
simply format it as Tahoma. No subsequent deletion is required and the
requisite used range is established.
That said, discussion as to the merits or demerits of one approach over
another is moot to the extent that the solution represents the resolution
of
a problem that I would not expect to encounter.
---
Regards,
Norman
"swatsp0p" wrote in message
...
What happens if you put an entry in cell IV65536 then did:
Select a single cell | Hit the F5 function key | Special | Check Blanks
|
OK
With the blank cells now selected, apply your desired formatting.
Once formatted, delete the entry in IV65536?
"Norman Jones" wrote:
Hi Paul,
If you really to format ALL empty cells on each worksheet, you can try
the
following:
Sub Tester()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
If Intersect(.UsedRange, .Cells(Rows.Count, _
Columns.Count)) Is Nothing Then
.Cells(Rows.Count, Columns.Count).Font.Name = "Tahoma"
End If
On Error Resume Next
.Cells.SpecialCells(xlBlanks).Font.Name = "Tahoma"
On Error GoTo 0
End With
Next sh
End Sub
This is not likely to be excessively fast!
---
Regards,
Norman
"Paul Black" wrote in message
...
Thanks Myrna,
I tried your Suggestion, it Worked for ALL Blank Cells from "A1" to
the Last Cell with Something in. It Ignored from that Cell to Cell
IV65536.
All the Best
Paul
Myrna Larson wrote in message
. ..
Edit/Goto, click the Special button. Select Blanks. Then apply the
formatting
you want. To do this in a macro, turn on the macro recorder, do it
manually,
stop the recorder and look at the code it generated.
On 9 Sep 2004 15:04:30 -0700, (Paul Black)
wrote:
Hi Tom,
Thanks for the Reply.
Your Macro does change ALL the Blank Cells ( A1:IV65536 )to
"Tahoma",
Unfortunately it also Changes Cells that have Data in. I Only want
Blank Cells to be Changed.
All the Best
Paul
"Tom Ogilvy" wrote in message
...
Sub Tester()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub
Although, it seems to me if you set the font for the normal
style
to
Tahoma,
it should make that the default for blank cells unless the have
previously
been set to a different font.
Another think you could do is go to a sheet that is formatted
the
way
you
want it. click on the intersection and do Edit=Copy, go to
this
sheet,
select A1 and do Edit=PasteSpecial and select formats. If
that
does
more
than you want, you can close the workbook without saving
changes.
--
Regards,
Tom Ogilvy
"Paul Black" wrote in message
...
Hi Norman,
Thanks for the Reply.
I First tried the Manual Method, and this did work Between
Cells
A1
and the Last Active Cell, But it Ignored Everything Between
the
Last
Active Cell and Cell IV65536.
I then tried the Macro and Got Exactly the Same Results.
All the Best
Paul
"Norman Jones" wrote in
message
...
Hi Paul,
Manually:
Select a single cell | Hit the F5 function key | Special |
Check
Blanks
OK
With the blank cells now selected, apply your desired
formatting.
Repeat for each worksheet.
Programmatically, try:
Sub Tester()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.SpecialCells(xlBlanks).Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub
---
Regards,
Norman
"Paul Black" wrote in message
...
Hi Everyone,
I have been given a Spreadsheet which needs Updating.
The Current ( Default on Excel for the Person that the
Sheet
Belongs
to ) Font is Set to "Ariel". I have Managed to Change the
Column
Letters and Row Numbers from "Ariel" to "Tahoma" by using
"Format",
"Style" and then Modifying the Font.
All the Cells in the Spreadsheet ( and Workbook ) that
have
Information and Formulas in are Either "Tahoma" or "Comic
Sans
MS"
Font.
What I would Ideally like is a way of Changing ALL the
Empty
"Ariel"
Cells ( A1:IV65536) to Empty "Tahoma" Cells in Both the
Worksheet AND
the Workbook.
I would like a Method of Achieving this Manually AND with
a
Macro if
Possible Please.
I am using XP and XL2002.
All the Best
Paul