Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|