add blank rows
Hi
I have 3 columns contains date 4 example below(left) ,but with missing dates, would you please help me how can i add row or cell blank between them and get right columns col1 col2 col3 col1 col2 col3 1 10 01 1961 1 10 11 1961 2 11 01 1961 2 11 11 1961 3 14 01 1961 3 4 18 01 1961 4 5 05 02 1961 5 14 11 1961 6 7 8 9 18 11 1961 thank's best regards raymon |
add blank rows
Public Sub ProcessData()
Dim i As Long Dim iLastRow As Long Dim dte1 As Date Dim dte2 As Date With ActiveSheet iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = iLastRow - 1 To 1 Step -1 dte1 = DateSerial(Cells(i, "C").Value, _ Cells(i, "B").Value, Cells(i, "A").Value) dte2 = DateSerial(Cells(i + 1, "C").Value, _ Cells(i + 1, "B").Value, Cells(i + 1, "A").Value) If dte2 - dte1 1 Then Rows(i + 1).Resize(dte2 - dte1 - 1).Insert End If Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "climate" wrote in message ... Hi I have 3 columns contains date 4 example below(left) ,but with missing dates, would you please help me how can i add row or cell blank between them and get right columns col1 col2 col3 col1 col2 col3 1 10 01 1961 1 10 11 1961 2 11 01 1961 2 11 11 1961 3 14 01 1961 3 4 18 01 1961 4 5 05 02 1961 5 14 11 1961 6 7 8 9 18 11 1961 thank's best regards raymon |
add blank rows
"Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim iLastRow As Long Dim dte1 As Date Dim dte2 As Date With ActiveSheet iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = iLastRow - 1 To 1 Step -1 dte1 = DateSerial(Cells(i, "C").Value, _ Cells(i, "B").Value, Cells(i, "A").Value) dte2 = DateSerial(Cells(i + 1, "C").Value, _ Cells(i + 1, "B").Value, Cells(i + 1, "A").Value) If dte2 - dte1 1 Then Rows(i + 1).Resize(dte2 - dte1 - 1).Insert End If Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "climate" wrote in message ... Hi I have 3 columns contains date 4 example below(left) ,but with missing dates, would you please help me how can i add row or cell blank between them and get right columns col1 col2 col3 col1 col2 col3 1 10 01 1961 1 10 11 1961 2 11 01 1961 2 11 11 1961 3 14 01 1961 3 4 18 01 1961 4 5 05 02 1961 5 14 11 1961 6 7 8 9 18 11 1961 thank's best regards raymon your response is excellent thank you bob |
All times are GMT +1. The time now is 11:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com