Select and Copy data in column.

[mick]

Anyone know of a way I can do the following. I have a
column (B) on my spreadsheet with a given persons name in
every other cell. eg.

Col A Col B Col C
xxxx Smith xxxx
xxxx xxxx
xxxx Jones xxxx
xxxx xxxx
xxxx Smith xxxx
xxxx xxxx

The row immediately underneath the name also contains data
pertaining to the named person in the row above i.e in my
example above row number 2 also pertains to the person
Smith, row number 4 also pertains to the person Jones,
etc, etc.

I am trying to copy the persons name into the cell
directly underneath so I am then able to sort by column B
to give me all the data pertaining to a particular
person. At the moment if I sort then all the rows
with 'empty' cells in column B are included in the sort
but at the end. Is there a quick way I can copy each name
in column B into the cell directly below it.

Thanks.

 

[Don Guillett]

try this
Sub copynamesdn()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("b2:b" & lr)
If c.Offset(1) = "" Then c.Offset(1) = c
Next
End Sub

 

[Andy B]

Hi

Select the column / range. Then Edit / Go to / Special / Blanks. This will
select all of the blank cells in the range. Type = and hit the up arrow and
then type Ctrl Enter. To fix these values use Copy then Paste Special /
Values on the range.

 

[Rollin_Again]

A simple macro will take care of what you want


PUBLIC SUB COPYNAME()

DO WHILE ACTIVECELL.ROW <= RANGE(\"B65536\").END(XLUP).ROW

ACTIVECELL.OFFSET(1, 0).VALUE = ACTIVECELL.VALUE
ACTIVECELL.OFFSET(2, 0).SELECT

LOOP

END SU


Rolli