Using a macro:
In a worksheet I would like for a row to be inserted if in a column the next
number is different.
example: in Column E
Before After
03279090 03279090
03279090 03279090
03299880
03299880 03299880
03299880 03299880
03299880
TFTH,
Tom
Using a macro:
In a worksheet I would like for a row to be inserted if in a column the next
number is different.
example: in Column E
Before After
03279090 03279090
03279090 03279090
03299880
03299880 03299880
03299880 03299880
03299880
TFTH,
Tom
To, the following macro should do your job:
Sub InsertRows()
StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 5 'Change the 1 to the column where your data is
i = StartRow + 1
While Cells(i, DataColumn) <> ""
If Cells(i, DataColumn) <> Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
i = i + 1
End If
i = i + 1
Wend
End Sub
-------------------------------------
To run it:
Alt+F11 for the VB editor
menu command Insert | Module
Paste the code above
>From Excel: Alt+F8
Or without VBA...
Enter this formula at B2 and copy down:
=--(A1=A2)
Copy > Paste Special > Value
Go To > Special > Column Differences
Insert > Entire Row
This seems to work except when there is a single instance interspersed in the
data.
If there is a single instance, it appears to insert 2 rows above the 1st
difference and none above the 2nd.
3279090
3279090 TRUE
3280000 FALSE
3299880 FALSE
3299880 TRUE
3299880 TRUE
Otherwise, that was a very interesting trick.
--
Kevin Vaughn
"Herbert Seidenberg" wrote:
> Or without VBA...
> Enter this formula at B2 and copy down:
> =--(A1=A2)
> Copy > Paste Special > Value
> Go To > Special > Column Differences
> Insert > Entire Row
>
>
I noticed I hadn't entered my formula exactly like yours, but even after
making the change, result was the same (I hadn't used the --() construct.)
--
Kevin Vaughn
"Kevin Vaughn" wrote:
> This seems to work except when there is a single instance interspersed in the
> data.
> If there is a single instance, it appears to insert 2 rows above the 1st
> difference and none above the 2nd.
>
> 3279090
> 3279090 TRUE
>
>
> 3280000 FALSE
> 3299880 FALSE
> 3299880 TRUE
> 3299880 TRUE
>
> Otherwise, that was a very interesting trick.
> --
> Kevin Vaughn
>
>
> "Herbert Seidenberg" wrote:
>
> > Or without VBA...
> > Enter this formula at B2 and copy down:
> > =--(A1=A2)
> > Copy > Paste Special > Value
> > Go To > Special > Column Differences
> > Insert > Entire Row
> >
> >
Kevin,
Thanks for pointing out the exception.
Here is a procedure that tolerates non-repeating data.
Enter this formula into B2 and copy down:
=--NOT((A1<>A2)*(B1=1))
Enter this formula into C2 and copy down:
=--(--(A1=A2)=B2)
Select the data in B and C and
Copy > Paste Special > Value
Go To > Special > Column Differences
Insert > Entire Row
This now opens up a whole lot of possibilities, like
inserting blanks at alternate rows.
Herb
That works for me up until the point where I actually insert rows. When I go
to special / column differences, I can tell that it is selecting the rows
that should make it work (ones with differences,) but when I hit insert rows
(or insert entire rows) it does not insert rows where I expect it to. I am
using 2000. Is that the difference?
--
Kevin Vaughn
"Herbert Seidenberg" wrote:
> Kevin,
> Thanks for pointing out the exception.
> Here is a procedure that tolerates non-repeating data.
> Enter this formula into B2 and copy down:
> =--NOT((A1<>A2)*(B1=1))
> Enter this formula into C2 and copy down:
> =--(--(A1=A2)=B2)
> Select the data in B and C and
> Copy > Paste Special > Value
> Go To > Special > Column Differences
> Insert > Entire Row
>
> This now opens up a whole lot of possibilities, like
> inserting blanks at alternate rows.
> Herb
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks