help advice needed

Hi,

Below macro will do the job ...

Alt F11 Insert Module
Copy the code in module ..

Sub Names()
Dim i As Integer
Dim LastRow As Integer
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
Range("A" & i).Select
If Len(ActiveCell.Value) = 1 Then
ActiveCell.Offset(-1, 0).Copy
ActiveCell.Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Next i
End Sub


HTH
Cheers
Carim

hi thanks for your help but i am getting an error message

I have done as you said but when i run it it says

run time error '6'

overflow

hope you can help

thanks

Hi,


Have you checked the range ...
Macro runs on column A, adjust to your needs ...

Carim

You can also do so:

Dim i As Integer
Dim LastRow As Integer
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

nameToUse = ""
myRange = "a1:a" & LastRow
For Each myCell In Range(myRange)
If IsNumeric(myCell) Then
If nameToUse <> "" Then
'here I put the name on cells
myCell.Value = nameToUse
End If
Else
'here I store the name
nameToUse = myCell
End If

Next

Regards,
Antonio

Hi

Thansk for your replies

I think my original example wasnt a very good one

Rather than numeric, the cells under each persons name contain text. The column is indeed A as the first anser guessed

for example

Steven
Not Blocked
Not Purchased
Not Purchased
Mike
Not Purchased
Not Purchased
Not Purchased
Not Purchased
Not Purchased
Chris
Allowed
Not Blocked
Not Blocked

Sorry if i misled you at all

Thanks

Steven

Hi again,

can anyone help me at all?

Your help will be very much appreciated

many thanks

Steven

Hi,

Is it a process you will have to repeat ?

What are all the different possibilities of strings below the first names ?

Carim

The trouble with what you are asking is that you have to be able to get the VBA code to differentiate between a name and the other text you have in your cells. You will need to provide a fool proof method of doing this. Now you mentioned that the names are in blue, so we could use that to our advantage.

the following code relies on the name being BLUE as per the description when you hover over the coloured swatches. If this is not the colour of your text, then you will need to change this line

If Selection.Offset(X, 0).Font.ColorIndex = 5 Then

to the colorindex that refers to the colour of your names

Please Login or Register  to view this content.