VB code
in [General]
|
Prev: Mail merge
Next: inserting a worksheet
From: tsraj on 25 Mar 2010 20:19 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.
From: Bob Phillips on 25 Mar 2010 20:46 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" <tsraj(a)discussions.microsoft.com> wrote in message news:13262B7A-C985-4EB8-9D5E-79C04F05ACAF(a)microsoft.com... >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.
From: Gary Keramidas on 25 Mar 2010 20:44 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" <tsraj(a)discussions.microsoft.com> wrote in message news:13262B7A-C985-4EB8-9D5E-79C04F05ACAF(a)microsoft.com... >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.
From: FSt1 on 25 Mar 2010 21:28 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.
From: tsraj on 25 Mar 2010 22:11
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" <tsraj(a)discussions.microsoft.com> wrote in message > news:13262B7A-C985-4EB8-9D5E-79C04F05ACAF(a)microsoft.com... > >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. > > > . > |