Need macro to drag values down to blank cells
 

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

Need macro to drag values down to blank cells
 

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" wrote:

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

Need macro to drag values down to blank cells
 

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"

"dmoney" wrote:

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" wrote:

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

Need macro to drag values down to blank cells
 

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?


"dmoney" wrote:

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" wrote:

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