Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for inserting blank row
Is there a formula or macro that would automatically insert a blank row right
after a number repeats itself? The repeating numbers are in one column. This is a huge spreadsheet and a real pain to do this manually. Connie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for inserting blank row
There's a few steps you can follow to get where you want to be:
FIRST AND FOREMOST - WORK ON A COPY OF YOUR SPREADSHEET If your data do not have column headings, insert a new row immediately above the data and add headers. With the cursor in your data, use Data-Subtotals to have insert a subtotal on each change in your number column When it finishes adding the subtotals, click on the little #2 button just under the address box to display just the subtotals Select the entire displayed range Use Edit-Goto-Special and click on Visible Cells Only Delete those rows Click on the #3 button "Connie Martin" wrote: Is there a formula or macro that would automatically insert a blank row right after a number repeats itself? The repeating numbers are in one column. This is a huge spreadsheet and a real pain to do this manually. Connie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for inserting blank row
That's neat! I use the subtotal feature but never knew you could use it this
way! Thank you! "Duke Carey" wrote: There's a few steps you can follow to get where you want to be: FIRST AND FOREMOST - WORK ON A COPY OF YOUR SPREADSHEET If your data do not have column headings, insert a new row immediately above the data and add headers. With the cursor in your data, use Data-Subtotals to have insert a subtotal on each change in your number column When it finishes adding the subtotals, click on the little #2 button just under the address box to display just the subtotals Select the entire displayed range Use Edit-Goto-Special and click on Visible Cells Only Delete those rows Click on the #3 button "Connie Martin" wrote: Is there a formula or macro that would automatically insert a blank row right after a number repeats itself? The repeating numbers are in one column. This is a huge spreadsheet and a real pain to do this manually. Connie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for inserting blank row
Connie
Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) < Cells(i, 1) Then _ Cells(i, 1).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub As written, operates on Column A Gord Dibben Excel MVP On Thu, 17 Nov 2005 08:45:11 -0800, "Connie Martin" wrote: Is there a formula or macro that would automatically insert a blank row right after a number repeats itself? The repeating numbers are in one column. This is a huge spreadsheet and a real pain to do this manually. Connie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for inserting blank row
Works like a charm! Now, if the data were in another column other than A,
what do I change in this macro. I don't see any reference to column A but it's probably there written in another lanugage! Connie "Gord Dibben" wrote: Connie Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) < Cells(i, 1) Then _ Cells(i, 1).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub As written, operates on Column A Gord Dibben Excel MVP On Thu, 17 Nov 2005 08:45:11 -0800, "Connie Martin" wrote: Is there a formula or macro that would automatically insert a blank row right after a number repeats itself? The repeating numbers are in one column. This is a huge spreadsheet and a real pain to do this manually. Connie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for inserting blank row
Connie
Changes made to operate on Column C(3) For i = Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 3) < Cells(i, 3) Then _ Cells(i, 3).Resize(1, 1).EntireRow.Insert Or this to choose a column number. Sub InsertRow_At_Change() Dim i As Long Dim colno As Long colno = InputBox("Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord On Thu, 17 Nov 2005 12:11:04 -0800, "Connie Martin" wrote: Works like a charm! Now, if the data were in another column other than A, what do I change in this macro. I don't see any reference to column A but it's probably there written in another lanugage! Connie "Gord Dibben" wrote: Connie Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) < Cells(i, 1) Then _ Cells(i, 1).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub As written, operates on Column A Gord Dibben Excel MVP On Thu, 17 Nov 2005 08:45:11 -0800, "Connie Martin" wrote: Is there a formula or macro that would automatically insert a blank row right after a number repeats itself? The repeating numbers are in one column. This is a huge spreadsheet and a real pain to do this manually. Connie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for inserting blank row
Thank you very much. This works so slick! With a worksheet of sometimes
1000 rows long, what a pile of work this macro saves. Thanks again! Connie "Gord Dibben" wrote: Connie Changes made to operate on Column C(3) For i = Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 3) < Cells(i, 3) Then _ Cells(i, 3).Resize(1, 1).EntireRow.Insert Or this to choose a column number. Sub InsertRow_At_Change() Dim i As Long Dim colno As Long colno = InputBox("Enter a Column Number") With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1 If Cells(i - 1, colno) < Cells(i, colno) Then _ Cells(i, colno).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord On Thu, 17 Nov 2005 12:11:04 -0800, "Connie Martin" wrote: Works like a charm! Now, if the data were in another column other than A, what do I change in this macro. I don't see any reference to column A but it's probably there written in another lanugage! Connie "Gord Dibben" wrote: Connie Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) < Cells(i, 1) Then _ Cells(i, 1).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub As written, operates on Column A Gord Dibben Excel MVP On Thu, 17 Nov 2005 08:45:11 -0800, "Connie Martin" wrote: Is there a formula or macro that would automatically insert a blank row right after a number repeats itself? The repeating numbers are in one column. This is a huge spreadsheet and a real pain to do this manually. Connie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula problem. | Excel Worksheet Functions | |||
if blank formula | Excel Discussion (Misc queries) | |||
blank data field shows quote in formula, how to delete it? | Excel Discussion (Misc queries) | |||
Blank a cell using a formula | Excel Discussion (Misc queries) | |||
Return blank cell using a formula | New Users to Excel |