I have a spreadsheet with the following data:

Column A Column B
05:00 Item A
Item B
Item C
07:00 Item A
Item B
09:00 Item B
Item C

Each item corresponds with the time in the same block. What I want to
accomplish is have the time move down until it comes to the next
time. Shown below:

05:00 Item A
05:00 Item B
05:00 Item C
07:00 Item A
07:00 Item B
09:00 Item B
09:00 Item C

So it copies the time down until it comes to the next, non-empty
cell. What I'll then do is count the number of item a entries at
specific times (morning vs afternoon) and so on.

Highlight the cells in column A from A2 down to the last entry in
column B. Then press F5 (GoTo) and click Special then click on Blanks
- this should now highlight all the blank cells in column A. Begin to
enter a formula by typing = then click on A1 and then do CTRL-Enter.
This will fill the blanks with the value from the cell above.

Hope this helps.

Pete

On Feb 17, 3:18*pm, Fester <bscar...@gmail.com> wrote:
> I have a spreadsheet with the following data:
>
> Column A * Column B
> 05:00 * * * * Item A
> * * * * * * * * *Item B
> * * * * * * * * *Item C
> 07:00 * * * * Item A
> * * * * * * * * *Item B
> 09:00 * * * * Item B
> * * * * * * * * *Item C
>
> Each item corresponds with the time in the same block. *What I want to
> accomplish is have the time move down until it comes to the next
> time. *Shown below:
>
> 05:00 * * * * Item A
> 05:00 * * * * Item B
> 05:00 * * * * Item C
> 07:00 * * * * Item A
> 07:00 * * * * Item B
> 09:00 * * * * Item B
> 09:00 * * * * Item C
>
> So it copies the time down until it comes to the next, non-empty
> cell. *What I'll then do is count the number of item a entries at
> specific times (morning vs afternoon) and so on.

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Stantial()
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
For Each c In myrange
If c.Offset(1, 0).Value = "" Then
With c.Offset(1, 0)
.Value = c.Value
.NumberFormat = "h:mm"
End With
End If
Next
End Sub

Mike

"Fester" wrote:

> I have a spreadsheet with the following data:
>
> Column A Column B
> 05:00 Item A
> Item B
> Item C
> 07:00 Item A
> Item B
> 09:00 Item B
> Item C
>
> Each item corresponds with the time in the same block. What I want to
> accomplish is have the time move down until it comes to the next
> time. Shown below:
>
> 05:00 Item A
> 05:00 Item B
> 05:00 Item C
> 07:00 Item A
> 07:00 Item B
> 09:00 Item B
> 09:00 Item C
>
> So it copies the time down until it comes to the next, non-empty
> cell. What I'll then do is count the number of item a entries at
> specific times (morning vs afternoon) and so on.
>

Fantastic.

Your name should read:

Pete
is awesome!


On Feb 17, 10:34*am, Pete_UK <pashu...@auditel.net> wrote:
> Highlight the cells in column A from A2 down to the last entry in
> column B. Then press F5 (GoTo) and click Special then click on Blanks
> - this should now highlight all the blank cells in column A. Begin to
> enter a formula by typing = then click on A1 and then do CTRL-Enter.
> This will fill the blanks with the value from the cell above.
>
> Hope this helps.
>
> Pete
>
> On Feb 17, 3:18*pm, Fester <bscar...@gmail.com> wrote:
>
> > I have a spreadsheet with the following data:
>
> > Column A * Column B
> > 05:00 * * * * Item A
> > * * * * * * * * *Item B
> > * * * * * * * * *Item C
> > 07:00 * * * * Item A
> > * * * * * * * * *Item B
> > 09:00 * * * * Item B
> > * * * * * * * * *Item C
>
> > Each item corresponds with the time in the same block. *What I want to
> > accomplish is have the time move down until it comes to the next
> > time. *Shown below:
>
> > 05:00 * * * * Item A
> > 05:00 * * * * Item B
> > 05:00 * * * * Item C
> > 07:00 * * * * Item A
> > 07:00 * * * * Item B
> > 09:00 * * * * Item B
> > 09:00 * * * * Item C
>
> > So it copies the time down until it comes to the next, non-empty
> > cell. *What I'll then do is count the number of item a entries at
> > specific times (morning vs afternoon) and so on.

Well, thank you for the kind words - glad it worked for you.

Pete

On Feb 17, 3:43*pm, Fester <bscar...@gmail.com> wrote:
> Fantastic.
>
> Your name should read:
>
> Pete
> is awesome!
>