How to add a row

[NeedsExcelHelp4]

Hey all...

I have a simple question... hoping someone might know the answer.

I need to insert a row after every unique cell (the column is sorted) in a specific column.

The data starts in B4 and goes down an undetermined amount of rows (data is always being added / deleted)... all I want to do is to add a row after every 'group' in that column B.

I've tried Loops combined with If statements with no luck...

Thanks!

[Paul B]

NeedsExcelHelp4, here is one way,

Sub Insert_Row_In_ColumnB()
'insert 1 rows in column B, when data changes
Dim Number_of_rows As Long
Dim Rowinsert As Integer
Application.ScreenUpdating = False
Number_of_rows = Range("B65536").End(xlUp).Row
Rowinsert = 1
Range("B2").Select
Do Until Selection.Row = Number_of_rows + 1
If Selection.Value <> Selection.Offset(-1, 0).Value Then
Selection.EntireRow.Resize(Rowinsert).Insert
Number_of_rows = Number_of_rows + Rowinsert
Selection.Offset(Rowinsert + 1, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"NeedsExcelHelp4"
<[email protected]> wrote in
message news:[email protected]...
>
> Hey all...
>
> I have a simple question... hoping someone might know the answer.
>
> I need to insert a row after every unique cell (the column is sorted)
> in a specific column.
>
> The data starts in B4 and goes down an undetermined amount of rows
> (data is always being added / deleted)... all I want to do is to add a
> row after every 'group' in that column B.
>
> I've tried Loops combined with If statements with no luck...
>
> Thanks!
>
>
> --
> NeedsExcelHelp4
> ------------------------------------------------------------------------
> NeedsExcelHelp4's Profile:
> http://www.excelforum.com/member.php...o&userid=31898
> View this thread: http://www.excelforum.com/showthread...hreadid=517358
>

[NeedsExcelHelp4]

Hey Paul... thanks for taking the time to look at my question.

I'm getting an error at line:
If Selection.Value <> Selection.Offset(-1, 0).Value Then

since I'm very new with VBA, I can't figure out the reason for the error.

Did I have to change something in your code? I just copied and pasted your code into the module...

Thanks again.

The error says Type mismatch for that line... not sure what that means though!

[Tom Ogilvy]

Since you wanted to start in B4, did you change

Range("B2").Select
to
Range("B5").Select

did that help?

if not

What is the error? Is it a type mismatch error?

Do you have any error values in your cells in column B?

Are you using xl97?

Where is the selection when you get the error

--
Regards,
Tom Ogilvy


"NeedsExcelHelp4"
<[email protected]> wrote in
message news:[email protected]...
>
> Hey Paul... thanks for taking the time to look at my question.
>
> I'm getting an error at line:
> If Selection.Value <> Selection.Offset(-1, 0).Value Then
>
> since I'm very new with VBA, I can't figure out the reason for the
> error.
>
> Did I have to change something in your code? I just copied and pasted
> your code into the module...
>
> Thanks again.
>
>
> --
> NeedsExcelHelp4
> ------------------------------------------------------------------------
> NeedsExcelHelp4's Profile:
http://www.excelforum.com/member.php...o&userid=31898
> View this thread: http://www.excelforum.com/showthread...hreadid=517358
>

[Paige]

Tom/Paul, I tried this code also. Interestingly, it inserted the rows
properly but did error out at the same place. My data started on row 1, so I
kept B2 as B2. Am using Excel 2002. Any ideas on what is wrong, as I can
use this code also, even though I didn't enter the original question.
Thanks....Paige

"Tom Ogilvy" wrote:

> Since you wanted to start in B4, did you change
>
> Range("B2").Select
> to
> Range("B5").Select
>
> did that help?
>
> if not
>
> What is the error? Is it a type mismatch error?
>
> Do you have any error values in your cells in column B?
>
> Are you using xl97?
>
> Where is the selection when you get the error
>
> --
> Regards,
> Tom Ogilvy
>
>
> "NeedsExcelHelp4"
> <[email protected]> wrote in
> message news:[email protected]...
> >
> > Hey Paul... thanks for taking the time to look at my question.
> >
> > I'm getting an error at line:
> > If Selection.Value <> Selection.Offset(-1, 0).Value Then
> >
> > since I'm very new with VBA, I can't figure out the reason for the
> > error.
> >
> > Did I have to change something in your code? I just copied and pasted
> > your code into the module...
> >
> > Thanks again.
> >
> >
> > --
> > NeedsExcelHelp4
> > ------------------------------------------------------------------------
> > NeedsExcelHelp4's Profile:
> http://www.excelforum.com/member.php...o&userid=31898
> > View this thread: http://www.excelforum.com/showthread...hreadid=517358
> >
>
>
>