How to add a row
 

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

How to add a row
 

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"
<NeedsExcelHelp4.23y5yp_1141143018.6369@excelfor um-nospam.com wrote in
message news:NeedsExcelHelp4.23y5yp_1141143018.6369@excelf orum-nospam.com...

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

How to add a row
 

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 th
error.

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

Thanks again

--
NeedsExcelHelp
-----------------------------------------------------------------------
NeedsExcelHelp4's Profile: http://www.excelforum.com/member.php...fo&userid=3189
View this thread: http://www.excelforum.com/showthread.php?threadid=51735

How to add a row
 

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"
<NeedsExcelHelp4.23y7co_1141144804.0296@excelfor um-nospam.com wrote in
message news:NeedsExcelHelp4.23y7co_1141144804.0296@excelf orum-nospam.com...

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

How to add a row
 

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"
<NeedsExcelHelp4.23y7co_1141144804.0296@excelfor um-nospam.com wrote in
message news:NeedsExcelHelp4.23y7co_1141144804.0296@excelf orum-nospam.com...

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