Hi Charles
I have only just returned to my post and seen your suggestion. I can report
the following on the table of 6,212 rows and 6 columns I was using to test:

this took 2.58 seconds:
If Not Cells(2, 13) = "" Then
lastcol = 12
Else
lastcol = 13
End If
For Each rng In tbl
If Len(rng) = 0 Then rng = "."
Next

A hybrid of the above and Replace took 0.78 seconds
For i = 7 To lastcol
Set tbl = Range(Cells(2, i), Cells(realLastRow, i))
If Not Cells(Rows.Count, i).End(xlUp).Row = 1 Then
For Each rng In tbl
If Len(rng) = 0 Then rng = "."
Next
Else
tbl.Replace "", ".", xlPart, xlByColumns, False
End If
Next

Your suggestion took 0.42 seconds, the best yet <g

Thanks for that.

Geoff


"Charles Williams" wrote:

Looks like the reason your loop was slow was that you were processing the
range cell by cell.

Try reading the data into a variant array, processing the variant array and
putting the variant array back: something like

dim tbl as variant
dim j as long
dim k as long
dim lCalcSave as long

Application.Screenupdating=false
lcalcsave=application.calculation
application.calculation=xlManual

tbl = range(cells(2, 1), cells(lastrow, 8)

For k=lbound(tbl,2) to ubound(tbl,2)
For j=lbound(tbl,1) to ubound(tbl,1)
If Len(tbl(j,k)) = 0 Then tbl(j,k) = "."
Next j
next k

range(cells(2, 1), cells(lastrow, 8)=tbl


Application.Screenupdating=true
application.calculation=lcalcsave

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Geoff" wrote in message
...
Sorry, meant to put tbl.Replace "", ".", xlPart, xlByColumns, False

Geoff

"Geoff" wrote:

Hi Rick
I tried all sorts before posting. The reason for pursuing this is one
purely of processing time. Before saving the file as a dbf4 I need to
insert
dots where there is no record. The table is always 8 columns but could
be
anything up to 62,000 rows.

In tests this construct was taking 3.80 seconds for 8 cols x 6,200 rows
tbl = range(cells(2, 1), cells(lastrow, 8)
For Each rng In tbl
If Len(rng) = 0 Then rng = "."
Next

Whereas using this, takes 0.69 seconds
tbl.Replace "", "*", xlPart, xlByColumns, False

Multiply the difference by say 50 wbooks in a folder and it all adds up.

Geoff

"Rick Rothstein (MVP - VB)" wrote:

I don't think there is a way around this problem. The Replace method
you are
using duplicates the Replace item found on Excel's menu bar under the
Edit
item. If you leave the "Find what" field blank and put 01 in the
"Replace
with" field, the leading zero will be dropped no matter whether you
pre-format the selection to be replaced as text or if you set the
"Replace
with" Format to Text. The only way I can get your code to do what you
want
is if you set kycode equal to "'01" (there is an apostrophe in front of
the
0). By the way, Edit/Replace works correctly if you put '01 in the
"Replace
with" field also.

Rick


"Geoff" wrote in message
...
Hi
For speed I am trying to use Replace, on a range object, to insert a
string
but the replacement string is displayed as a single character whereas
it
should be 2.



Say kycode = "01" this code displays it correctly as 01

Dim kycode As String
Dim tbl as Range, rng as Range
Dim finalrecords as Long

Range("H:H").NumberFormat = "@"
If Not kycode = "" Then
Set tbl = Range("H2:H" & finalrecords + 1)
For Each rng In tbl
rng = kycode '''=01
Next
End If

This code displays keycode as 1
Range("H:H").NumberFormat = "@"
If Not kycode = "" Then
Set tbl = Range("H2:H" & finalrecords + 1)
tbl.Replace "", kycode, xlWhole, xlByColumns, False '''=1
End If

The benefits of Replace in terms of speed are worthwhile considering
the
number of records to search. Any help would be appreciated.

Geoff