ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   add blank rows (https://www.excelbanter.com/excel-worksheet-functions/160452-add-blank-rows.html)

climate

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

Bob Phillips

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




climate

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