Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > macro adding rows
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default macro adding rows
hi,

I have a worksheet that contains data formatted with name start date and
time and finish date and time. I am looking for a macro to insert a row after
each date for example

21/09/2009 13:54 21/09/2009 14:05
21/09/2009 16:59 21/09/2009 18:11

22/09/2009 06:57 22/09/2009 07:15
22/09/2009 07:59 22/09/2009 08:15
22/09/2009 09:29 22/09/2009 09:35
22/09/2009 10:52 22/09/2009 11:14
22/09/2009 12:35 22/09/2009 13:02
22/09/2009 15:04 22/09/2009 15:15
22/09/2009 16:49 22/09/2009 17:18

23/09/2009 06:58 23/09/2009 07:14
23/09/2009 07:51 23/09/2009 08:20
23/09/2009 09:00 23/09/2009 09:18
23/09/2009 12:07 23/09/2009 12:13
23/09/2009 12:40 23/09/2009 12:52
23/09/2009 16:29 23/09/2009 16:50
23/09/2009 17:07 23/09/2009 17:25

As you can see there could be multiple start times for any day any help or
advice then please let me know as it is taking far to long to do manually

Thanks,

James
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default macro adding rows
Hi

Try this:

Sub InsertRow()
TargetCol = "A"
Firstrow = 2 'Headings in row 1
LastRow = Cells(Rows.Count, TargetCol).End(xlUp).Row

For r = LastRow - 1 To Firstrow Step -1
If Cells(r, TargetCol).Value < Cells(r + 1, TargetCol) Then
Rows(r + 1).Insert
End If
Next
End Sub

Regards,
Per

"dobbers" skrev i meddelelsen
...
hi,

I have a worksheet that contains data formatted with name start date and
time and finish date and time. I am looking for a macro to insert a row
after
each date for example

21/09/2009 13:54 21/09/2009 14:05
21/09/2009 16:59 21/09/2009 18:11

22/09/2009 06:57 22/09/2009 07:15
22/09/2009 07:59 22/09/2009 08:15
22/09/2009 09:29 22/09/2009 09:35
22/09/2009 10:52 22/09/2009 11:14
22/09/2009 12:35 22/09/2009 13:02
22/09/2009 15:04 22/09/2009 15:15
22/09/2009 16:49 22/09/2009 17:18

23/09/2009 06:58 23/09/2009 07:14
23/09/2009 07:51 23/09/2009 08:20
23/09/2009 09:00 23/09/2009 09:18
23/09/2009 12:07 23/09/2009 12:13
23/09/2009 12:40 23/09/2009 12:52
23/09/2009 16:29 23/09/2009 16:50
23/09/2009 17:07 23/09/2009 17:25

As you can see there could be multiple start times for any day any help or
advice then please let me know as it is taking far to long to do manually

Thanks,

James

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default macro adding rows
I'm assuming that the second set of dates will be the same as the first
(diiferent times obviously) and that the first set are in column A

the dateserial() function is used to strip off the time, so that the dates
can be compared directly

open the VBA editor (ALT+F11) then add a new code module (Insert/Module) and
paste in the following code:

Option Explicit
Sub Main()
Dim thisRow As Long
Dim dn As Date
Dim dn_1 As Date
For thisRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
dn = Cells(thisRow, 1)
dn = DateSerial(Year(dn), Month(dn), Day(dn))
dn_1 = Cells(thisRow - 1, 1)
dn_1 = DateSerial(Year(dn_1), Month(dn_1), Day(dn_1))
If dn < dn_1 Then
Rows(thisRow).Insert
End If
Next
End Sub



"dobbers" wrote:

hi,

I have a worksheet that contains data formatted with name start date and
time and finish date and time. I am looking for a macro to insert a row after
each date for example

21/09/2009 13:54 21/09/2009 14:05
21/09/2009 16:59 21/09/2009 18:11

22/09/2009 06:57 22/09/2009 07:15
22/09/2009 07:59 22/09/2009 08:15
22/09/2009 09:29 22/09/2009 09:35
22/09/2009 10:52 22/09/2009 11:14
22/09/2009 12:35 22/09/2009 13:02
22/09/2009 15:04 22/09/2009 15:15
22/09/2009 16:49 22/09/2009 17:18

23/09/2009 06:58 23/09/2009 07:14
23/09/2009 07:51 23/09/2009 08:20
23/09/2009 09:00 23/09/2009 09:18
23/09/2009 12:07 23/09/2009 12:13
23/09/2009 12:40 23/09/2009 12:52
23/09/2009 16:29 23/09/2009 16:50
23/09/2009 17:07 23/09/2009 17:25

As you can see there could be multiple start times for any day any help or
advice then please let me know as it is taking far to long to do manually

Thanks,

James
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default macro adding rows
Hi,

Tried both of the options and bothe returned a mismatch error

Thanks,

James

"Per Jessen" wrote:

Hi

Try this:

Sub InsertRow()
TargetCol = "A"
Firstrow = 2 'Headings in row 1
LastRow = Cells(Rows.Count, TargetCol).End(xlUp).Row

For r = LastRow - 1 To Firstrow Step -1
If Cells(r, TargetCol).Value < Cells(r + 1, TargetCol) Then
Rows(r + 1).Insert
End If
Next
End Sub

Regards,
Per

"dobbers" skrev i meddelelsen
...
hi,

I have a worksheet that contains data formatted with name start date and
time and finish date and time. I am looking for a macro to insert a row
after
each date for example

21/09/2009 13:54 21/09/2009 14:05
21/09/2009 16:59 21/09/2009 18:11

22/09/2009 06:57 22/09/2009 07:15
22/09/2009 07:59 22/09/2009 08:15
22/09/2009 09:29 22/09/2009 09:35
22/09/2009 10:52 22/09/2009 11:14
22/09/2009 12:35 22/09/2009 13:02
22/09/2009 15:04 22/09/2009 15:15
22/09/2009 16:49 22/09/2009 17:18

23/09/2009 06:58 23/09/2009 07:14
23/09/2009 07:51 23/09/2009 08:20
23/09/2009 09:00 23/09/2009 09:18
23/09/2009 12:07 23/09/2009 12:13
23/09/2009 12:40 23/09/2009 12:52
23/09/2009 16:29 23/09/2009 16:50
23/09/2009 17:07 23/09/2009 17:25

As you can see there could be multiple start times for any day any help or
advice then please let me know as it is taking far to long to do manually

Thanks,

James


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default macro adding rows
we both assumed your line was two dates with times in two separate columns.
I stated that was my presumption.
isn't that the case? What are the details of the mismatch?



"dobbers" wrote:

Hi,

Tried both of the options and bothe returned a mismatch error

Thanks,

James

"Per Jessen" wrote:

Hi

Try this:

Sub InsertRow()
TargetCol = "A"
Firstrow = 2 'Headings in row 1
LastRow = Cells(Rows.Count, TargetCol).End(xlUp).Row

For r = LastRow - 1 To Firstrow Step -1
If Cells(r, TargetCol).Value < Cells(r + 1, TargetCol) Then
Rows(r + 1).Insert
End If
Next
End Sub

Regards,
Per

"dobbers" skrev i meddelelsen
...
hi,

I have a worksheet that contains data formatted with name start date and
time and finish date and time. I am looking for a macro to insert a row
after
each date for example

21/09/2009 13:54 21/09/2009 14:05
21/09/2009 16:59 21/09/2009 18:11

22/09/2009 06:57 22/09/2009 07:15
22/09/2009 07:59 22/09/2009 08:15
22/09/2009 09:29 22/09/2009 09:35
22/09/2009 10:52 22/09/2009 11:14
22/09/2009 12:35 22/09/2009 13:02
22/09/2009 15:04 22/09/2009 15:15
22/09/2009 16:49 22/09/2009 17:18

23/09/2009 06:58 23/09/2009 07:14
23/09/2009 07:51 23/09/2009 08:20
23/09/2009 09:00 23/09/2009 09:18
23/09/2009 12:07 23/09/2009 12:13
23/09/2009 12:40 23/09/2009 12:52
23/09/2009 16:29 23/09/2009 16:50
23/09/2009 17:07 23/09/2009 17:25

As you can see there could be multiple start times for any day any help or
advice then please let me know as it is taking far to long to do manually

Thanks,

James


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default macro adding rows
Hi,

The dates are in columns D and E i have other information before them such
as names vehicle registrations and a project code

Thanks,

james

"Patrick Molloy" wrote:

we both assumed your line was two dates with times in two separate columns.
I stated that was my presumption.
isn't that the case? What are the details of the mismatch?



"dobbers" wrote:

Hi,

Tried both of the options and bothe returned a mismatch error

Thanks,

James

"Per Jessen" wrote:

Hi

Try this:

Sub InsertRow()
TargetCol = "A"
Firstrow = 2 'Headings in row 1
LastRow = Cells(Rows.Count, TargetCol).End(xlUp).Row

For r = LastRow - 1 To Firstrow Step -1
If Cells(r, TargetCol).Value < Cells(r + 1, TargetCol) Then
Rows(r + 1).Insert
End If
Next
End Sub

Regards,
Per

"dobbers" skrev i meddelelsen
...
hi,

I have a worksheet that contains data formatted with name start date and
time and finish date and time. I am looking for a macro to insert a row
after
each date for example

21/09/2009 13:54 21/09/2009 14:05
21/09/2009 16:59 21/09/2009 18:11

22/09/2009 06:57 22/09/2009 07:15
22/09/2009 07:59 22/09/2009 08:15
22/09/2009 09:29 22/09/2009 09:35
22/09/2009 10:52 22/09/2009 11:14
22/09/2009 12:35 22/09/2009 13:02
22/09/2009 15:04 22/09/2009 15:15
22/09/2009 16:49 22/09/2009 17:18

23/09/2009 06:58 23/09/2009 07:14
23/09/2009 07:51 23/09/2009 08:20
23/09/2009 09:00 23/09/2009 09:18
23/09/2009 12:07 23/09/2009 12:13
23/09/2009 12:40 23/09/2009 12:52
23/09/2009 16:29 23/09/2009 16:50
23/09/2009 17:07 23/09/2009 17:25

As you can see there could be multiple start times for any day any help or
advice then please let me know as it is taking far to long to do manually

Thanks,

James


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default macro adding rows
so with my code you changed the column to 4 and/or with Per's you changed the
target column to D ?
what was the mismatch relating to?

"dobbers" wrote:

Hi,

The dates are in columns D and E i have other information before them such
as names vehicle registrations and a project code

Thanks,

james

"Patrick Molloy" wrote:

we both assumed your line was two dates with times in two separate columns.
I stated that was my presumption.
isn't that the case? What are the details of the mismatch?



"dobbers" wrote:

Hi,

Tried both of the options and bothe returned a mismatch error

Thanks,

James

"Per Jessen" wrote:

Hi

Try this:

Sub InsertRow()
TargetCol = "A"
Firstrow = 2 'Headings in row 1
LastRow = Cells(Rows.Count, TargetCol).End(xlUp).Row

For r = LastRow - 1 To Firstrow Step -1
If Cells(r, TargetCol).Value < Cells(r + 1, TargetCol) Then
Rows(r + 1).Insert
End If
Next
End Sub

Regards,
Per

"dobbers" skrev i meddelelsen
...
hi,

I have a worksheet that contains data formatted with name start date and
time and finish date and time. I am looking for a macro to insert a row
after
each date for example

21/09/2009 13:54 21/09/2009 14:05
21/09/2009 16:59 21/09/2009 18:11

22/09/2009 06:57 22/09/2009 07:15
22/09/2009 07:59 22/09/2009 08:15
22/09/2009 09:29 22/09/2009 09:35
22/09/2009 10:52 22/09/2009 11:14
22/09/2009 12:35 22/09/2009 13:02
22/09/2009 15:04 22/09/2009 15:15
22/09/2009 16:49 22/09/2009 17:18

23/09/2009 06:58 23/09/2009 07:14
23/09/2009 07:51 23/09/2009 08:20
23/09/2009 09:00 23/09/2009 09:18
23/09/2009 12:07 23/09/2009 12:13
23/09/2009 12:40 23/09/2009 12:52
23/09/2009 16:29 23/09/2009 16:50
23/09/2009 17:07 23/09/2009 17:25

As you can see there could be multiple start times for any day any help or
advice then please let me know as it is taking far to long to do manually

Thanks,

James


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default macro adding rows
hi patrick,

the code now works thank you so much

James

"Patrick Molloy" wrote:

so with my code you changed the column to 4 and/or with Per's you changed the
target column to D ?
what was the mismatch relating to?

"dobbers" wrote:

Hi,

The dates are in columns D and E i have other information before them such
as names vehicle registrations and a project code

Thanks,

james

"Patrick Molloy" wrote:

we both assumed your line was two dates with times in two separate columns.
I stated that was my presumption.
isn't that the case? What are the details of the mismatch?



"dobbers" wrote:

Hi,

Tried both of the options and bothe returned a mismatch error

Thanks,

James

"Per Jessen" wrote:

Hi

Try this:

Sub InsertRow()
TargetCol = "A"
Firstrow = 2 'Headings in row 1
LastRow = Cells(Rows.Count, TargetCol).End(xlUp).Row

For r = LastRow - 1 To Firstrow Step -1
If Cells(r, TargetCol).Value < Cells(r + 1, TargetCol) Then
Rows(r + 1).Insert
End If
Next
End Sub

Regards,
Per

"dobbers" skrev i meddelelsen
...
hi,

I have a worksheet that contains data formatted with name start date and
time and finish date and time. I am looking for a macro to insert a row
after
each date for example

21/09/2009 13:54 21/09/2009 14:05
21/09/2009 16:59 21/09/2009 18:11

22/09/2009 06:57 22/09/2009 07:15
22/09/2009 07:59 22/09/2009 08:15
22/09/2009 09:29 22/09/2009 09:35
22/09/2009 10:52 22/09/2009 11:14
22/09/2009 12:35 22/09/2009 13:02
22/09/2009 15:04 22/09/2009 15:15
22/09/2009 16:49 22/09/2009 17:18

23/09/2009 06:58 23/09/2009 07:14
23/09/2009 07:51 23/09/2009 08:20
23/09/2009 09:00 23/09/2009 09:18
23/09/2009 12:07 23/09/2009 12:13
23/09/2009 12:40 23/09/2009 12:52
23/09/2009 16:29 23/09/2009 16:50
23/09/2009 17:07 23/09/2009 17:25

As you can see there could be multiple start times for any day any help or
advice then please let me know as it is taking far to long to do manually

Thanks,

James


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default macro adding rows
great

"dobbers" wrote:

hi patrick,

the code now works thank you so much

James

"Patrick Molloy" wrote:

so with my code you changed the column to 4 and/or with Per's you changed the
target column to D ?
what was the mismatch relating to?

"dobbers" wrote:

Hi,

The dates are in columns D and E i have other information before them such
as names vehicle registrations and a project code

Thanks,

james

"Patrick Molloy" wrote:

we both assumed your line was two dates with times in two separate columns.
I stated that was my presumption.
isn't that the case? What are the details of the mismatch?



"dobbers" wrote:

Hi,

Tried both of the options and bothe returned a mismatch error

Thanks,

James

"Per Jessen" wrote:

Hi

Try this:

Sub InsertRow()
TargetCol = "A"
Firstrow = 2 'Headings in row 1
LastRow = Cells(Rows.Count, TargetCol).End(xlUp).Row

For r = LastRow - 1 To Firstrow Step -1
If Cells(r, TargetCol).Value < Cells(r + 1, TargetCol) Then
Rows(r + 1).Insert
End If
Next
End Sub

Regards,
Per

"dobbers" skrev i meddelelsen
...
hi,

I have a worksheet that contains data formatted with name start date and
time and finish date and time. I am looking for a macro to insert a row
after
each date for example

21/09/2009 13:54 21/09/2009 14:05
21/09/2009 16:59 21/09/2009 18:11

22/09/2009 06:57 22/09/2009 07:15
22/09/2009 07:59 22/09/2009 08:15
22/09/2009 09:29 22/09/2009 09:35
22/09/2009 10:52 22/09/2009 11:14
22/09/2009 12:35 22/09/2009 13:02
22/09/2009 15:04 22/09/2009 15:15
22/09/2009 16:49 22/09/2009 17:18

23/09/2009 06:58 23/09/2009 07:14
23/09/2009 07:51 23/09/2009 08:20
23/09/2009 09:00 23/09/2009 09:18
23/09/2009 12:07 23/09/2009 12:13
23/09/2009 12:40 23/09/2009 12:52
23/09/2009 16:29 23/09/2009 16:50
23/09/2009 17:07 23/09/2009 17:25

As you can see there could be multiple start times for any day any help or
advice then please let me know as it is taking far to long to do manually

Thanks,

James


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding rows dynamically via macro sleepingdragon2k2 Excel Programming 1 November 28th 06 08:45 PM
Adding rows dynamically via macro sleepingdragon2k2 Excel Programming 2 November 28th 06 07:26 PM
Adding rows dynamically via macro sleepingdragon2k2 Excel Programming 0 November 28th 06 06:46 PM
adding rows to spreadsheet after macro is written iamn94 Excel Programming 2 April 25th 05 01:48 PM
macro - adding rows to a column that is summed HGood Excel Discussion (Misc queries) 2 December 1st 04 03:28 PM


All times are GMT +1. The time now is 08:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement