Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to stop index match returning data where lookup cell empty | Excel Worksheet Functions | |||
Copy cell to empty cells below. | Excel Programming | |||
Copy Data From Filled to Empty Cells | Excel Discussion (Misc queries) | |||
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other? | Excel Programming | |||
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other? | Excel Programming |