Ok then, just expanding on Don's answer a bit to use specialcells where you are
able, but looping through all if not:-
Sub LeadingLtr()
Dim rng As Range
Dim c As Range
Dim cnt As Long
Dim cntdat As Long
Set rng = Cells.SpecialCells(xlCellTypeConstants, 23)
If rng.Areas.Count 1 Then
For Each c In rng
c.Value = "HD:" & c
Next
Else
Set rng = ActiveSheet.UsedRange
cnt = rng.Rows.Count * rng.Columns.Count
cntdat = 1
For Each c In rng
Application.StatusBar = "Approx " & Round((cntdat / cnt) * 100, 0) & "%
complete"
cntdat = cntdat + 1
If Len(c) 0 Then c.Value = "HD:" & c
Next
Application.StatusBar = False
End If
End Sub
Special cells work a lot quicker than looping through the entire range, but you
have to be careful that if you have more than 8,192 non-contiguous areas and you
use the specialcells method it will return just one area and you won't want this
to be applied to all the blanks within that area, so the routine checks to see
how many areas you have, and if you actually have more than 8,192 Specialcells
will report that you only have 1, so it will default to looping, but if less
then it will use the specialcells method.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"HSFJKB" wrote in message
...
All the data is text, the HD: needs to show up only in the cells that have a
value. The reason being is the xl data is exported to a web database where
the HD: identifeies the property of the field. Values with nothing show
nothing.
"Ken Wright" wrote:
Is your data all text? If so do you need the HD: bit to be real or will
visual
only suffice. If visual is good enough then select column, do format / cells
/
custom and use "HD:"@ as the custom format.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"HSFJKB" wrote in message
...
For every cell in a coloumn that has a value I would like it to be preceded
by one given value. For example if values in a certain coloum were a,b,c,d
.
I would like it to read HD:a , HD:b , HD:c , HD:d but the balnk cells must
reamin blank.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.789 / Virus Database: 534 - Release Date: 07/11/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.789 / Virus Database: 534 - Release Date: 07/11/2004