Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 23rd 05, 04:35 PM
fiero84
 
Posts: n/a
Default 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   Report Post  
Old March 23rd 05, 06:44 PM
Bernd Plumhoff
 
Posts: n/a
Default

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   Report Post  
Old March 23rd 05, 07:30 PM
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
Old March 24th 05, 03:57 PM
fiero84
 
Posts: n/a
Default

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   Report Post  
Old March 24th 05, 04:01 PM
fiero84
 
Posts: n/a
Default

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   Report Post  
Old March 25th 05, 06:59 AM
Bernd Plumhoff
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to browse for workbook, pick up data and looping uriel78 Excel Worksheet Functions 1 March 10th 05 12:37 PM
Activate a macro to insert a row and copy the formuals from the rows above to the blank row oil_driller Excel Discussion (Misc queries) 1 February 11th 05 03:30 PM
Looping Macro to Find and Mark Big Tony New Users to Excel 8 January 26th 05 09:07 PM
Inserting Blank Rows Macro? Michael Saffer Excel Worksheet Functions 2 November 9th 04 06:23 PM
macro to not print blank value rows in invoice Dave Excel Worksheet Functions 1 November 3rd 04 07:21 PM


All times are GMT +1. The time now is 07:41 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017