Copy Data to Cells, but Stop at Next Non-Empty Cell

  • Thread starter Thread starter Fester
  • Start date Start date
F

Fester

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
 
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
 
Back
Top