Help!

Insert lines at each change in value

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions RSS
Next:  How do I sum the value of a range of cells withou..  
Author Message
LisaVH
External


Since: Nov 02, 2006
Posts: 10



PostPosted: Tue Dec 12, 2006 5:00 pm    Post subject: Insert lines at each change in value
Archived from groups: microsoft>public>excel>misc (more info?)

I need to insert a line at each change in "property Number" in a very long
list of data. Can I set it up to automatically insert a line at each change
(like subtotal, but without the subtotal data)?
Back to top
Zone
External


Since: May 18, 2006
Posts: 26



PostPosted: Tue Dec 12, 2006 5:35 pm    Post subject: Re: Insert lines at each change in value [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Lisa, How about this? Copy the sub into a standard module and run it.
It assumes you want to work with the active worksheet. It also
assumes that your property numbers are in column A and that you don't
want to look any higher than row 2. HTH, James

Sub InsertAtChange()
Dim r As Long
For r = Cells(65536, "a").End(xlUp).Row To 3 Step -1
If Cells(r, "a") <> "" And Cells(r, "a") <> Cells(r - 1, "a")
Then
Rows(r).EntireRow.Insert
End If
Next r
End Sub

LisaVH wrote:
> I need to insert a line at each change in "property Number" in a very long
> list of data. Can I set it up to automatically insert a line at each change
> (like subtotal, but without the subtotal data)?
Back to top
LisaVH
External


Since: Nov 02, 2006
Posts: 10



PostPosted: Tue Dec 12, 2006 5:43 pm    Post subject: Re: Insert lines at each change in value [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Thank You!

"Zone" wrote:

> Lisa, How about this? Copy the sub into a standard module and run it.
> It assumes you want to work with the active worksheet. It also
> assumes that your property numbers are in column A and that you don't
> want to look any higher than row 2. HTH, James
>
> Sub InsertAtChange()
> Dim r As Long
> For r = Cells(65536, "a").End(xlUp).Row To 3 Step -1
> If Cells(r, "a") <> "" And Cells(r, "a") <> Cells(r - 1, "a")
> Then
> Rows(r).EntireRow.Insert
> End If
> Next r
> End Sub
>
> LisaVH wrote:
> > I need to insert a line at each change in "property Number" in a very long
> > list of data. Can I set it up to automatically insert a line at each change
> > (like subtotal, but without the subtotal data)?
>
>
Back to top
Zone
External


Since: May 18, 2006
Posts: 26



PostPosted: Wed Dec 13, 2006 1:04 pm    Post subject: Re: Insert lines at each change in value [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Glad it worked for you. James
LisaVH wrote:
> Thank You!
>
> "Zone" wrote:
>
> > Lisa, How about this? Copy the sub into a standard module and run it.
> > It assumes you want to work with the active worksheet. It also
> > assumes that your property numbers are in column A and that you don't
> > want to look any higher than row 2. HTH, James
> >
> > Sub InsertAtChange()
> > Dim r As Long
> > For r = Cells(65536, "a").End(xlUp).Row To 3 Step -1
> > If Cells(r, "a") <> "" And Cells(r, "a") <> Cells(r - 1, "a")
> > Then
> > Rows(r).EntireRow.Insert
> > End If
> > Next r
> > End Sub
> >
> > LisaVH wrote:
> > > I need to insert a line at each change in "property Number" in a very long
> > > list of data. Can I set it up to automatically insert a line at each change
> > > (like subtotal, but without the subtotal data)?
> >
> >
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum