Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert formula
Hi all,
Is there a way to insert a row automatically in between a different number when the number change?. Like creating a macro or something. I do not want to do it manually. I have about 200 rows to insert. Please see example below. I want to be able to insert a row in between 2000045 and 200061 also between 2000061 and 2000069 and so on..... 2000045 2000045 2000045 2000061 2000061 2000061 2000069 2000069 2000076 2000076 2000076 2000087 2000087 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert formula
Try one of these macros..with data in ColA data starting at Row 2
If you are new to macros.. --Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected macro() Sub InsertBlankRowAfterEachGroup() Dim lngRow As Long, intTemp As Integer lngRow = 2 Do If Range("A" & lngRow + 1) < Range("A" & lngRow) Then _ Rows(lngRow + 1).Insert: lngRow = lngRow + 1 lngRow = lngRow + 1 Loop Until lngRow Cells(Rows.Count, "A").End(xlUp).Row End Sub Sub InsertBlankRowsbetweenChangingValues() Dim lngRow As Long Application.ScreenUpdating = False For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < "" And Range("A" & lngRow - 1) < "" And _ Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).Insert Next Application.ScreenUpdating = True End Sub -- Jacob (MVP - Excel) "John" wrote: Hi all, Is there a way to insert a row automatically in between a different number when the number change?. Like creating a macro or something. I do not want to do it manually. I have about 200 rows to insert. Please see example below. I want to be able to insert a row in between 2000045 and 200061 also between 2000061 and 2000069 and so on..... 2000045 2000045 2000045 2000061 2000061 2000061 2000069 2000069 2000076 2000076 2000076 2000087 2000087 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert formula
This is amazing. Thank you so much Jacob.
"Jacob Skaria" wrote: Try one of these macros..with data in ColA data starting at Row 2 If you are new to macros.. --Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected macro() Sub InsertBlankRowAfterEachGroup() Dim lngRow As Long, intTemp As Integer lngRow = 2 Do If Range("A" & lngRow + 1) < Range("A" & lngRow) Then _ Rows(lngRow + 1).Insert: lngRow = lngRow + 1 lngRow = lngRow + 1 Loop Until lngRow Cells(Rows.Count, "A").End(xlUp).Row End Sub Sub InsertBlankRowsbetweenChangingValues() Dim lngRow As Long Application.ScreenUpdating = False For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < "" And Range("A" & lngRow - 1) < "" And _ Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).Insert Next Application.ScreenUpdating = True End Sub -- Jacob (MVP - Excel) "John" wrote: Hi all, Is there a way to insert a row automatically in between a different number when the number change?. Like creating a macro or something. I do not want to do it manually. I have about 200 rows to insert. Please see example below. I want to be able to insert a row in between 2000045 and 200061 also between 2000061 and 2000069 and so on..... 2000045 2000045 2000045 2000061 2000061 2000061 2000069 2000069 2000076 2000076 2000076 2000087 2000087 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert formula
Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007 Dim LastRow As Long Dim X As Long LastRow = Cells(Rows.Count, 1).End(xlUp).Row Application.ScreenUpdating = False For X = LastRow To 2 Step -1 If Cells(X, 1).Value < Cells(X - 1, 1).Value Then If Cells(X, 1).Value < "" Then If Cells(X - 1, 1).Value < "" Then Cells(X, 1).EntireRow.Insert End If End If End If Next X Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Tue, 25 May 2010 08:09:01 -0700, John wrote: Hi all, Is there a way to insert a row automatically in between a different number when the number change?. Like creating a macro or something. I do not want to do it manually. I have about 200 rows to insert. Please see example below. I want to be able to insert a row in between 2000045 and 200061 also between 2000061 and 2000069 and so on..... 2000045 2000045 2000045 2000061 2000061 2000061 2000069 2000069 2000076 2000076 2000076 2000087 2000087 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to insert a formula? | Excel Discussion (Misc queries) | |||
insert formula | Excel Worksheet Functions | |||
Insert a value into formula | Excel Discussion (Misc queries) | |||
How to insert a formula..but... | Excel Discussion (Misc queries) | |||
insert a new row by formula | Excel Worksheet Functions |