Prefill column

[SITCFanTN]

Below is a sample of the data I download to a spreadsheet. I need to write a
macro to fill in the empty cells in column A with the data from the previous
cell in the column with data. I know I didn't explain that well but in the
example below, I need TF48 to fill in Col A for rows 3,4 & 5. Then T16A
would fill in row 7 and T505 in row 9 and 10. I hope I've explained this
sufficiently. Thanks in advance for your help.

1 A B C D
2 TF48 $100.00 ABC 20091116
3 $ 50.00 DTR 20091116
4 $ 25.00 PLO 20091116
5 $ 75.00 FRD 20091116
6 T16A $100.35 UYE 20091116
7 $124.55 PLE 20091116
8 T505 $109.00 REW 20091115
9 $876.90 JLO 20091113
10 $234.90 REW 20091116

 

[Bernard Liengme]

This seems to work for me

Sub tryme()
mylast = Cells(Cells.Rows.Count, "B").End(xlUp).Row
mytext = Cells(1, "A")
For j = 2 To mylast
If Cells(j, "A") = "" Then
Cells(j, "A") = mytext
Else
mytext = Cells(j, "A")
End If
Next j
End Sub

Save file before trying it - just incase!
best wishes

 

[Rick Rothstein]

This macro should be pretty speedy...

Sub FillInTheBlanks()
Dim Area As Range, LastRow As Long
Const ColLetter As String = "A"
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
For Each Area In Columns(ColLetter)(1).Resize(LastRow). _
SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(-1).Value
Next
End Sub

 

[chg]

What about that?

Sub FillBlanksColA()

Dim rng As Range
Dim rCell As Range
Set rng = Range(Cells(1, 1), Cells(1, 2).End(xlDown))

For Each rCell In rng
If rCell.Value = "" Then
rCell.Value = rCell.Offset(-1, 0).Value
End If
Next

End Sub

 

[Mike]

try this
Option Explicit
Sub test()
Const WORKSHEET_NAME As String = "Sheet1"
Const WHATCOLUMN As String = "B"
Dim i As Long
Dim sString As String
i = 1
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(WORKSHEET_NAME)

Dim rng As Range
Set rng = ws.Range(ws.Cells(2, WHATCOLUMN), ws.Cells(Rows.Count,
WHATCOLUMN).End(xlUp))

Dim Cell As Range
For Each Cell In rng
If Cell.Offset(0, -1).Value <> "" Then
sString = Cell.Offset(0, -1).Value
Else
Cell.Offset(0, -1).Value = sString
End If
Next

End Sub

 

[Rick Rothstein]

I guess I should point out why I said my solution "should be pretty
speedy"... the difference between my loop and the others that have been
posted so far is the number of iterations that will be performed. For your
posted example data, the macro I posted will iterate 3 times (once for each
blank area) whereas the other solution's loops will iterate 9 times (once
per cell). I would also note that my calculation for the LastRow does not
require you to know in advance which column will contain the row with the
last piece of data in it... my code finds the last filled row of data no
matter what column it is in.