VBA Code to copy data down to blank cells 1

[xrayr]

I have the following code that I'm trying to utilize to copy data down to blank cells in Excel:

'COPYING DATA DOWN INTO EACH ROW

Range("A2").Select
Do Until ActiveCell.Offset(0, 1) = ""
If ActiveCell.Offset(1, 0) = "" Then
ActiveWorkbook.Names.Add Name:="TASK", RefersToR1C1:=ActiveCell
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell <> ""
If ActiveCell.Offset(1, 1) = "" Then
ActiveCell.Offset(1, 0).Select
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
ActiveCell.Offset(-1, 0).Select
ActiveWorkbook.Names.Add Name:="TASK2", RefersToR1C1:=ActiveCell
Range("TASK").Select
Selection.Copy
Range("TASK:TASK2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlDown).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Selection.ClearContents

Range("A2").Select

End Sub

The coding will not work unless I change it to:

'COPYING DATA DOWN INTO EACH ROW

Range("A2").Select
Do Until ActiveCell = ""

When I change the coding to: Do Until ActiveCell = "", the code will run until down to maximum row limit of Excel.

Any ideas?

An example of what my Excel sheet looks like is:

Apr-10 54 67 87
89 99 99
67 45 45
Aug-10 87 99 14
55 77 99
77 99 89

I'm looking for the date data (Apr-10, etc) to copy down to the blank rows. It should also stop when there truly is no data left in the the column.

Please help...

 

[Bong]

This seems extremely cumbersome for just copying data.
You start in A2 and it seems like you're checking until column B has a blank cell. Is that right. Anyway, I would go about it by defining the range of cells into which you want to copy (using union(rng1, rng2, ...) if the range is non-contiguous). Then just define the values of that range to be equal to the value of A2. How are you defining the target cells?

_________________
Bob Rashkin

 

[SkipVought]

I'm a huge VBA user, but I'd almost never code this kind of thing; rather I'd use a formula on the sheet in a separate column to generate the data.

I seldom get PivotTable-like outputs to manipulate. If I got them on a regular basis, I'd go to the source (person or data) and try to get the output I need.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xrayr]

I'm not checking column B for the final blank cell. That was just from trying many different things to get the coding to work.

Do you have an example of the coding needed for defining the range and then assigning their values?

The blank cells can be 3 in a row or 150 in a row. Different at each report run. Need coding that can accomodate this variability. Each time there is a non-blank cell is where there is a "date" (Apr-10, etc) change.

Thank you in adavance!

 

[SkipVought]

Sub CopyValues()
    Dim r As Range, r2 As Long
    
    'assume that the first data cell is A2
    Set r = [A2]
    Do
      If r.Offset(1).Value = "" Then
        'there are more than 1 row for this item
        r2 = r.End(xlDown).Row - 1
        If r2 > UsedRange.Rows.Count Then Exit Do
        Range(r, Cells(r2, r.Column)).Value = r.Value
        
        Set r = r.End(xlDown)
      End If
    Loop
    r2 = UsedRange.Rows.Count
    Range(r, Cells(r2, r.Column)).Value = r.Value
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xrayr]

Thank you, Skip!!! It works GREAT!!!!