| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB code
I am using Office 2007. In Excel, I like to write a code in Visual Basic to
copy the contents of the cell above into a blank cell down below and repeat the procedure until it reaches the bottom of the data. All the cells are not blank cells. Only a few of them are blank cells. Example of the column 03/01/2008 01/01/2008 02/01/2008 03/01/2008 03/01/2008 03/01/2008 11/01/2007 12/01/2007 01/01/2008 02/01/2008 I like to copy 01/01/2008 into the empty cell down below. Then move to the next blank cell and copy 02/01/2008 into the empty one. Repeat the procedure until it reaches the bottom of the data, in my example 02/01/2008. Any help in this regard is very much appreciated. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB code
not sure if they are actually blank, but i used column A in my code
Sub copy_dates() Dim ws As Worksheet Dim lastrow As Long Dim cell As Range Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For Each cell In Range("A2:A" & lastrow) If cell.Value = "" Then cell.Value = cell.Offset(-1).Value End If Next End Sub -- Gary Keramidas Excel 2003 "tsraj" wrote in message ... I am using Office 2007. In Excel, I like to write a code in Visual Basic to copy the contents of the cell above into a blank cell down below and repeat the procedure until it reaches the bottom of the data. All the cells are not blank cells. Only a few of them are blank cells. Example of the column 03/01/2008 01/01/2008 02/01/2008 03/01/2008 03/01/2008 03/01/2008 11/01/2007 12/01/2007 01/01/2008 02/01/2008 I like to copy 01/01/2008 into the empty cell down below. Then move to the next blank cell and copy 02/01/2008 into the empty one. Repeat the procedure until it reaches the bottom of the data, in my example 02/01/2008. Any help in this regard is very much appreciated. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB code
Try
Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 2 To LastRow - 1 If .Cells(i, "A").Value2 = "" Then .Cells(i, "A").Value2 = .Cells(i - 1, "A").Value2 End If Next i End With End Sub -- HTH Bob "tsraj" wrote in message ... I am using Office 2007. In Excel, I like to write a code in Visual Basic to copy the contents of the cell above into a blank cell down below and repeat the procedure until it reaches the bottom of the data. All the cells are not blank cells. Only a few of them are blank cells. Example of the column 03/01/2008 01/01/2008 02/01/2008 03/01/2008 03/01/2008 03/01/2008 11/01/2007 12/01/2007 01/01/2008 02/01/2008 I like to copy 01/01/2008 into the empty cell down below. Then move to the next blank cell and copy 02/01/2008 into the empty one. Repeat the procedure until it reaches the bottom of the data, in my example 02/01/2008. Any help in this regard is very much appreciated. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB code
hi
try this Sub copdats() Dim r As Range Dim c As Range Dim lr As Long lr = Cells(Rows.Count, "A").End(xlUp).Row Set r = Range("A2:A" & lr) For Each c In r If c.Value = "" Then c.Value = c.Offset(-1, 0).Value End If Next c End Sub regards FSt1 "tsraj" wrote: I am using Office 2007. In Excel, I like to write a code in Visual Basic to copy the contents of the cell above into a blank cell down below and repeat the procedure until it reaches the bottom of the data. All the cells are not blank cells. Only a few of them are blank cells. Example of the column 03/01/2008 01/01/2008 02/01/2008 03/01/2008 03/01/2008 03/01/2008 11/01/2007 12/01/2007 01/01/2008 02/01/2008 I like to copy 01/01/2008 into the empty cell down below. Then move to the next blank cell and copy 02/01/2008 into the empty one. Repeat the procedure until it reaches the bottom of the data, in my example 02/01/2008. Any help in this regard is very much appreciated. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB code
Thank you very much. It worked. Please let me have the code if I have
multiple columns where the procedure has to be applied. I am new to writing VB codes. Thanks again. "Bob Phillips" wrote: Try Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 2 To LastRow - 1 If .Cells(i, "A").Value2 = "" Then .Cells(i, "A").Value2 = .Cells(i - 1, "A").Value2 End If Next i End With End Sub -- HTH Bob "tsraj" wrote in message ... I am using Office 2007. In Excel, I like to write a code in Visual Basic to copy the contents of the cell above into a blank cell down below and repeat the procedure until it reaches the bottom of the data. All the cells are not blank cells. Only a few of them are blank cells. Example of the column 03/01/2008 01/01/2008 02/01/2008 03/01/2008 03/01/2008 03/01/2008 11/01/2007 12/01/2007 01/01/2008 02/01/2008 I like to copy 01/01/2008 into the empty cell down below. Then move to the next blank cell and copy 02/01/2008 into the empty one. Repeat the procedure until it reaches the bottom of the data, in my example 02/01/2008. Any help in this regard is very much appreciated. . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB code
To make it work for other columns, simply change the "A" entries in the code
to the column identifier that you wish to work with. Note that the code is only working with the cells in the indicated column. It is possible to move an entire row of data based on the same type of conditions, but we would need to know if that is what you wish to do. "tsraj" wrote: Thank you very much. It worked. Please let me have the code if I have multiple columns where the procedure has to be applied. I am new to writing VB codes. Thanks again. "Bob Phillips" wrote: Try Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 2 To LastRow - 1 If .Cells(i, "A").Value2 = "" Then .Cells(i, "A").Value2 = .Cells(i - 1, "A").Value2 End If Next i End With End Sub -- HTH Bob "tsraj" wrote in message ... I am using Office 2007. In Excel, I like to write a code in Visual Basic to copy the contents of the cell above into a blank cell down below and repeat the procedure until it reaches the bottom of the data. All the cells are not blank cells. Only a few of them are blank cells. Example of the column 03/01/2008 01/01/2008 02/01/2008 03/01/2008 03/01/2008 03/01/2008 11/01/2007 12/01/2007 01/01/2008 02/01/2008 I like to copy 01/01/2008 into the empty cell down below. Then move to the next blank cell and copy 02/01/2008 into the empty one. Repeat the procedure until it reaches the bottom of the data, in my example 02/01/2008. Any help in this regard is very much appreciated. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
| Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
| Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
| Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
| copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
Linear Mode
