Autofill down over blank cells until new data, and repeat.

[Brian Albright]

Hey everyone,

I searched around a bit but couldn't find an answer to what I'm trying
to do exactly, so maybe I can get a bit of help here?

I have a list of data that looks something like this:

Month Mgr Item
January Rob 4
- - 5
- Ted 3
- - 4
February Alex 3
- - 4
- - 5
- Rob 7
- - 9
March Ted 3
- - 4
- - 6

where '-' represents a blank cell.

In order to put this data into a pivot table, it seems I need to fill
in all the data for all the dashes with redundant data. I'd need to
fill down January 3 times, February twice, and so on. I would also
have to fill the names down to match the data.

Is there an easy way or a macro I can write that will fill the cells
down until it reaches the next data cell, and then continue to fill
that down with the new data and so on to fill the table in order to
put it into a Pivot table?

Thanks in advance,
Brian

 

[Per Jessen]

Hey everyone,

I searched around a bit but couldn't find an answer to what I'm trying
to do exactly, so maybe I can get a bit of help here?

I have a list of data that looks something like this:

Month      Mgr     Item
January    Rob    4
-              -         5
-              Ted     3
-              -         4
February  Alex    3
-              -         4
-              -         5
-             Rob     7
-             -          9
March     Ted      3
-             -          4
-             -          6

where '-' represents a blank cell.

In order to put this data into a pivot table, it seems I need to fill
in all the data for all the dashes with redundant data.  I'd need to
fill down January 3 times, February twice, and so on.  I would also
have to fill the names down to match the data.

Is there an easy way or a macro I can write that will fill the cells
down until it reaches the next data cell, and then continue to fill
that down with the new data and so on to fill the table in order to
put it into a Pivot table?

Thanks in advance,
Brian

Hi Brian

With your table in columns A:C and Headings in row 1, this should fill
in the redundant data.

Sub FillIn()
LastRow = Range("C1").End(xlDown).Row

For r = 3 To LastRow
If Cells(r, 1).Value = "" Then Cells(r, 1).Value = Cells(r - 1,
1).Value
If Cells(r, 2).Value = "" Then Cells(r, 2).Value = Cells(r - 1,
2).Value
Next
End Sub

Regards,
Per