VB code

[tsraj]

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.

 

[Gary Keramidas]

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

 

[Bob Phillips]

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

 

[FSt1]

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]

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.

 

[JLatham]

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.