Home 
Search 
Today's Posts 
#1




Looping Macro That adds a blank row between different part #'s
I do several reports everyday that require me to put a blank row between part
numbers that are different. So, I'd like a Macro that would compare two cells, if they are equal to each other, do nothing but keep moving down the list and comparing. If the cells are different, one part number might be 100200 and the other part number might be 655422, then put a blank row between those two parts and move on to compare the rest of the list. I'm pretty good with Excel and formulas  just new to working with Macros  but am finding out they are pretty similar... just not sure of the correct commands... All I need is to see an example... Many Thanks!!! 
#2




Option Explicit
Sub a() Dim i As Long 'Not Integer because it could grow 32768 Sheets("Sheet1").Select i = 2 Do While Not IsEmpty(Cells(i, 1)) If Cells(i  1, 1).Value < Cells(i, 1).Value Then Range("A1").Offset(i  1).EntireRow.Insert i = i + 1 End If i = i + 1 Loop End Sub HTH, Bernd 
#3




Operating on chosen column.
Sub InsertRow_At_Change() Dim i As Long Dim colno As Long With Application .Calculation = xlManual .ScreenUpdating = False End With colno = InputBox("Enter a Column Number") 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, colno).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord Dibben Excel MVP On Wed, 23 Mar 2005 08:35:03 0800, "fiero84" wrote: I do several reports everyday that require me to put a blank row between part numbers that are different. So, I'd like a Macro that would compare two cells, if they are equal to each other, do nothing but keep moving down the list and comparing. If the cells are different, one part number might be 100200 and the other part number might be 655422, then put a blank row between those two parts and move on to compare the rest of the list. I'm pretty good with Excel and formulas  just new to working with Macros  but am finding out they are pretty similar... just not sure of the correct commands... All I need is to see an example... Many Thanks!!! 
#4




This WORKED PERFECTLY!!! You are an absolute Genius!!! Thank you! Thank
you! Thank you!!! I couldn't find a way to make it work with my header row, so I just cut and pasted my header row to the end of the sheet and when I was done, just cut and pasted them back to the top row. Other than that, I just filled in the name of my "Sheet1" tab and presto!!!! Thanks again!!! You have just saved me and my boss and my company hours of labor. "fiero84" wrote: I do several reports everyday that require me to put a blank row between part numbers that are different. So, I'd like a Macro that would compare two cells, if they are equal to each other, do nothing but keep moving down the list and comparing. If the cells are different, one part number might be 100200 and the other part number might be 655422, then put a blank row between those two parts and move on to compare the rest of the list. I'm pretty good with Excel and formulas  just new to working with Macros  but am finding out they are pretty similar... just not sure of the correct commands... All I need is to see an example... Many Thanks!!! 
#5




Thank you for replying! I received another solution from a "Bernd Plumhoff"
that was a little bit shorter than your example and it did what I needed. But, I REALLY appreciate you taking the time to answer. I'm sure yours would have worked as well. "Gord Dibben" wrote: Operating on chosen column. Sub InsertRow_At_Change() Dim i As Long Dim colno As Long With Application .Calculation = xlManual .ScreenUpdating = False End With colno = InputBox("Enter a Column Number") 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, colno).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord Dibben Excel MVP On Wed, 23 Mar 2005 08:35:03 0800, "fiero84" wrote: I do several reports everyday that require me to put a blank row between part numbers that are different. So, I'd like a Macro that would compare two cells, if they are equal to each other, do nothing but keep moving down the list and comparing. If the cells are different, one part number might be 100200 and the other part number might be 655422, then put a blank row between those two parts and move on to compare the rest of the list. I'm pretty good with Excel and formulas  just new to working with Macros  but am finding out they are pretty similar... just not sure of the correct commands... All I need is to see an example... Many Thanks!!! 
#6




You are welcome.
If only one specific header has to be ignored, change: .... If Cells(i  1, 1).Value < Cells(i, 1).Value Then .... to If (Cells(i  1, 1).Value < Cells(i, 1).Value) and _ Left(Cells(i, 1).text,20)< "First 20 Chars of Header Row" Then for example (if you have to cross that bridge again). HTH, Bernd 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
macro to browse for workbook, pick up data and looping  Excel Worksheet Functions  
Activate a macro to insert a row and copy the formuals from the rows above to the blank row  Excel Discussion (Misc queries)  
Looping Macro to Find and Mark  New Users to Excel  
Inserting Blank Rows Macro?  Excel Worksheet Functions  
macro to not print blank value rows in invoice  Excel Worksheet Functions 