Copy Data to Cells, but Stop at Next Non-Empty Cell
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. |
Copy Data to Cells, but Stop at Next Non-Empty Cell
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 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. |
Copy Data to Cells, but Stop at Next Non-Empty Cell
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. |
Copy Data to Cells, but Stop at Next Non-Empty Cell
Fantastic.
Your name should read: Pete is awesome! On Feb 17, 10:34*am, Pete_UK 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 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. |
Copy Data to Cells, but Stop at Next Non-Empty Cell
Well, thank you for the kind words - glad it worked for you.
Pete On Feb 17, 3:43*pm, Fester wrote: Fantastic. Your name should read: Pete is awesome! |
All times are GMT +1. The time now is 08:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com