Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete blank rows (rows alternate data, blank, data, etc | Excel Discussion (Misc queries) | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
Get number of rows that data uses, including blank rows | Excel Discussion (Misc queries) | |||
Get number of rows that data takes up, including blank rows | Excel Worksheet Functions |