Copy cells down
My sample data should have looked like RC's example. Guess email spacing
got me again. Not sure how it lost all spacing.
--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
If your data is like this:
A
01/01/2005
01/02/2005
01/03/2005
01/04/2005
B
01/06/2005
01/07/2005
01/08/2005
01/09/2005
01/10/2005
then you can use this:
This assumes that the cells in column B that are next to populated cells
in
column A are blank.
Sub CC()
Dim rng As Range, rng1 As Range
Dim ar As Range
Set rng = Range("B8", Cells(Rows.Count, 2).End(xlUp))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlConstants)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
For Each ar In rng1.Areas
ar.Offset(0, -1).Value = ar(0, 0).Value
ar(0, 0).ClearContents
Next
Set rng = Range("A7", Cells(Rows.Count, 2).End(xlUp))
Set rng1 = Nothing
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
End Sub
--
Regards,
Tom Ogilvy
"tjtjjtjt" wrote in message
...
Here is what I have so far. It hangs after copying twice.
Sub TestCode()
For Each c In Range("A7:A200")
If c.Formula < "" Then
Do
c.Copy c.Offset(1, 0)
Loop While c.Offset(0, 1) < ""
Else
End If
Next c
End Sub
"tjtjjtjt" wrote:
I'm returning to Excel VBA after a brief introduction to it some
months
ago.
Using Excel 2000.
I have a report that I import into Excel. I want to transform it into
a
Excel List.
First I need to copy any value in Column A down while the cell in
Column
B
for the same row contains data.
I then need to clear the cell that was orignally copied.
Last, I need to take the information in Column A and delete all rows
in
the
UsedRange for which Column A is blank.
An example:
Cell A7 contains a name.
B8:B12 contain the dates the person will work.
I want the person's name to appear in A8:A12, and then clear cell A7.
Then, I would like Excel to find the next name and repeat the process.
The
first person will always be in A7, but names after that will be in
different
cells weekly.
I think I need some combination of offset, if and a do loop, but I've
been
unable to come up with anything that even gets close.
Any help would be greatly appreciated.
--
tj
|