Macro To Copy Down

  • Thread starter Thread starter Jamesa
  • Start date Start date
J

Jamesa

I want to copy data down a column in a ws until it finds
an existing text in the same column. It should then copy
that text down the same column until it finds the next
text entry and so on.
Would someone please help me with a macro or vb for this
task. I would be very grateful.

Hoping to hear from you soon.
JA
 
James,

The following code should do what you require.

If you have any queries let me know, I have tested it in
Excel 2002 and it works fine.

Yours sincerely,

Brent McIntyre

Create a CommandButton on the worksheet called
CommandButton_Process, then in VBA go to the worksheet you
put the button on and place the following code.

Public Sub CommandButton_Process_Click()

Dim Row_Count_Current As Long
Dim Row_Count_Final As Long
Dim Value_Current
Row_Count_Current = 1
Row_Count_Final = ActiveWorkbook.ActiveSheet.Cells
(Rows.Count, 1).End(xlUp).Row

While Row_Count_Current < Row_Count_Final + 1

If ActiveWorkbook.ActiveSheet.Range("A" &
Row_Count_Current) = "" Then

ActiveWorkbook.ActiveSheet.Range("A" &
Row_Count_Current) = Value_Current

Else

Value_Current =
ActiveWorkbook.ActiveSheet.Range("A" & Row_Count_Current)

End If

Row_Count_Current = Row_Count_Current + 1

Wend

End Sub
 
Jamesa, try

Sub CopyDown()
Const StartRowNo = 1
Const EndRowNo = 2000
Const ColumnNo = 1
Dim curText As String, curRowNo As Long

curRowNo = StartRowNo
Do While IsEmpty(Cells(curRowNo, ColumnNo)) And curRowNo <= EndRowNo
curRowNo = curRowNo + 1
Loop

Do While curRowNo <= EndRowNo
If IsEmpty(Cells(curRowNo, ColumnNo).Value) Then
Cells(curRowNo, ColumnNo).Value = curText
Else
curText = Cells(curRowNo, ColumnNo).Value
End If
curRowNo = curRowNo + 1
Loop
End Sub


Good luck
 
Back
Top