How to use auto fill in a macro

[PerryK]

I am just starting to learn about Visual Basic Commands in Macros.

I have a spreadsheet, that has a list of data in Column B.
The data in column B can vary in length
The first cell in column A has location name.

The data in "B" will continue until the next Loctaion starts in Column A

I am trying to figure out how to copy the location name in Column A and then
paste it down until it runs into the next Location Name.

This is an example of the data:

Location Product color
MD Red
Green
Orange
VA Blue
Brown
Black
Red
NC Violet
Orange
Brown
Green
Black


In this example, I need to copy the "MD" down until "VA" - then I need to
copy "VA" until "NC" then I need "NC" copied to the end of the list.

I cannot use a range of cells because the range varies for each location.

Is there a command that works like "Auto Fill"


Thanks

 

[Dave Peterson]

Debra Dalgleish shares a couple of techniques (including code):
http://contextures.com/xlDataEntry02.html
http://www.contextures.com/xlVideos01.html#FillBlanks

 

[Joel]

The is probably the easiest way but not the quickest. Moving down the
worksheet row by row is slower than other methods.

Sub copylocation()
RowCount = 2
Do While Range("B" & RowCount) <> ""
If Range("A" & RowCount) = "" Then
Range("A" & RowCount) = Location
Else
Location = Range("A" & RowCount)
End If
RowCount = RowCount + 1
Loop

End Sub

 

[Mike H]

Hi,

I am assuming the forst MD is in row 1. right click your sheet tab, view
code and paste this in and run it.

Sub Fill_EM_UP()
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("A2:A" & lastrow)
For Each C In MyRange
If C.Value = "" Then
C.Value = C.Offset(-1).Value
End If
Next
End Sub

Mike

 

[Mike H]

Hi,

I just noticed you have headers so the first MD is in row 2. Use this instead

Sub Fill_EM_UP()
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("A3:A" & lastrow)
For Each C In MyRange
If C.Value = "" Then
C.Value = C.Offset(-1).Value
End If
Next
End Sub

Mike