Need macro to drag values down to blank cells

[Greg Snidow]

Greetings all. I have a spreadsheet that I use to upload data to a SQL
Server using bulk insert. Column 1 is for a town name, but it is only
populated for the first row of every town. For example, if there are 10 rows
of data for Leesburg, only row 1 would have "Leesburg" in column 1, and rows
2-10 will be blank for column 1, until you get to the next set of towns.
Lets say the next 10 rows are for Tampa, starting at row 11. Column 1 at row
11 will say "Tampa", but rows 12-20 will be blank, until the next town
starts. What I do is drag each town down through the section for that town,
so that every row has the town populated. Is there a way to do this
programatically?

Greg

 

[dmoney]

This will do it, but you must put in the word stop at the bottom of your
spreadsheet in column a for it to work.

Sub fill()
'
Range("a1").Select
Do Until ActiveCell.Value = "stop"
If ActiveCell.Value <> "" Then
Dim a As String
a = ActiveCell.Address
ActiveCell.Offset(1, 0).Activate
Count = 1
End If
Do Until ActiveCell.Value <> ""
If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Activate
Count = Count + 1
End If
Loop
Dim b As String
ActiveCell.Offset(-1, 0).Activate
b = ActiveCell.Address
Range(a & ":" & b).FillDown
ActiveCell.Offset(1, 0).Activate
Loop
'
End Sub

 

[Greg Snidow]

Thanks dmoney. It worked like a charm. Incidentally, I found some code on
another post to calculate the last row, which came in nicely here. I just
had it autopopulate "stop" in the appropriate cell.

StopRow = [B65535].End(xlUp).Row
Set MyRng = Range("B2:B" & StopRow)
Range("A" & StopRow + 1).Select
ActiveCell.Value = "stop"

 

[Keep It Simple Stupid]

I tried using this and it doesn't fill in my blanks. It just scrolls through
and stops at the stop line.

I thought this would work for me because I have a text value in column A
with blanks under each - and I need those values to be dragged down through
the blank cells. Am I doing something wrong?

 

[Roger Govier]

Hi

The following code should do what you want.
Just select the range in column A as far as you wish to go, then run

Sub fillblanks()
Dim rownum As Long, i As Long

With Selection
rownum = .Rows.Count
End With
For i = 2 To rownum
If Cells(i, 1) = "" Then
Cells(i, 1) = Cells(i - 1, 1).Value
End If
Next i

End Sub