Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find number & insert rows
I would like to find a repeating number in a column and, except for the
first instance, insert rows to a row number. Specifically, no 1 may occur several times in a column. I want to find the second instance and insert whatever number of rows necassary (it will vary) to row 25. Then find the next number 1 and insert rows to 50 and so on. Any help appreciated. Steve wallis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find number & insert rows
Increase by 25 rows each time? And do you know, for instance, that the
second occurrence of the "1" will be found in a row that is less than 25? And so on for all subsequent occurrences? Without a doubt? HATH Otto "Steve Wallis" wrote in message ... I would like to find a repeating number in a column and, except for the first instance, insert rows to a row number. Specifically, no 1 may occur several times in a column. I want to find the second instance and insert whatever number of rows necassary (it will vary) to row 25. Then find the next number 1 and insert rows to 50 and so on. Any help appreciated. Steve wallis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find number & insert rows
Yes. Without a doubt.
Steve "Otto Moehrbach" wrote in message ... Increase by 25 rows each time? And do you know, for instance, that the second occurrence of the "1" will be found in a row that is less than 25? And so on for all subsequent occurrences? Without a doubt? HATH Otto "Steve Wallis" wrote in message ... I would like to find a repeating number in a column and, except for the first instance, insert rows to a row number. Specifically, no 1 may occur several times in a column. I want to find the second instance and insert whatever number of rows necassary (it will vary) to row 25. Then find the next number 1 and insert rows to 50 and so on. Any help appreciated. Steve wallis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find number & insert rows
Steve
Here is the first shot at it. I assumed your data was in Column A starting in A1. I also assumed that when you said: ". I want to find the second instance and insert whatever number of rows necessary (it will vary) to row 25. Then find the next number 1 and insert rows to 50 and so on." you meant that you wanted rows 25, 50, etc to be the last blank row in that group of blank rows. Is that right? Watch out for line wrap in this message. I'm also sending you, direct via email, a small file with this macro properly placed. HTH Otto Sub RowsAfter1() Dim RngColA As Range Dim c As Long Dim FoundCell As Range Dim FirstFoundCellRow As Long c = 0 Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp)) Set FoundCell = RngColA.Find(What:=Range("B1").Value, After:=RngColA(RngColA.Count), _ LookAt:=xlWhole) FirstFoundCellRow = FoundCell.Row Do c = c + 1 Set FoundCell = RngColA.Find(What:=Range("B1").Value, _ After:=FoundCell, LookAt:=xlWhole) If FoundCell.Row = FirstFoundCellRow Then Exit Sub FoundCell.Offset(1).Resize((25 * c) - FoundCell.Row).Insert Shift:=xlDown Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp)) Loop End Sub "Steve Wallis" wrote in message ... Yes. Without a doubt. Steve "Otto Moehrbach" wrote in message ... Increase by 25 rows each time? And do you know, for instance, that the second occurrence of the "1" will be found in a row that is less than 25? And so on for all subsequent occurrences? Without a doubt? HATH Otto "Steve Wallis" wrote in message ... I would like to find a repeating number in a column and, except for the first instance, insert rows to a row number. Specifically, no 1 may occur several times in a column. I want to find the second instance and insert whatever number of rows necassary (it will vary) to row 25. Then find the next number 1 and insert rows to 50 and so on. Any help appreciated. Steve wallis |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find number & insert rows
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array to find rows need to insert date criteria | Excel Discussion (Misc queries) | |||
Insert a specified number of rows dynamically | Excel Worksheet Functions | |||
Can I use "find / replace" to automatically "Insert Rows" in a do. | Excel Discussion (Misc queries) | |||
insert variable number of rows | Excel Programming | |||
insert 'x' number of rows??? | Excel Programming |