Macro for conditionally inserting rows - Excel

[notadba]

Hi,

This should hopefully be a quick one - if I knew how to do it.

I have a dataset that needds to be cleaned up by inserting a blank row when the ID number in the first column changes. There is no consistent row count for each ID.

Looks like

ID date attribute1 attribute2 etc
123 20060624 abc
123 20060624 def
123 20060714 abc
123 20060714 def
156 20060624 abc
156 20060624 def
189 20060714 abc
189 20060714 def
etc

Each tme there is a change in the ID field only, need a blank row inserted to split the records.

Result should look like:
123 20060624 abc
123 20060624 def
123 20060714 abc
123 20060714 def

156 20060624 abc
156 20060624 def

189 20060714 abc
189 20060714 def


Thanks in advance

 

[Rofeu]

Hi,

take a look at the Subtotal option.

(Data --> Subtotals, then specify your ID field for 'At each change in')

Cheers,

Roel

 

[SkipVought]

Hi,

I STRONGLY advise against inserting blank rows between your data. It destroys Excel's ability to work on a table.

Use Row Height, borders and shading for emphasis.

Skip,

_[red][/red]

 

[firefytr]

I'd like to second Skips mention of NOT inserting blank rows. Is this only for data presentation? If so, leave your source data alone (unless some of the other suggestions work for you, ie Subtotals) and have your summary data on another sheet (either via copying or formulas) where you can format it there. This leaves your source data where it should be. If you have a lot of data, you might want to think about an Access database.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[notadba]

Thanks all for the responses. This is for use on a relatively small data set (around 3000-5000 rows) and the data will be refreshed/dumped every few days.

This is only for presentation, and no real sub-totalling required. I have seen earlier responses to this type of question where it was suggested not to do this - but I really was looking for how to do this.

The sub-total is ok - just creates some garbage data that is not needed - but does give a break between the records.

 

[SkipVought]

"...only for presentation..."

I've seen ti many times before.

"Could you send me that sheet that was in your presentation?"

Feathers in the wind....

Skip,

_[red][/red]

 

[notadba]

While I understand the issue of not doing this, the question was how to - and then I can decide how to use the solution. Roels repsonse was helpful, and achieved a close result (thank you).

As to get access to the original dataset being available, isn't it just amazing how the sort function removes those blank rows. BTW It is not "for a presentation" - but for 'presentation' so I can look at the data with rows splitting the records for easy identification of employees.

I have come up with a solution in the meantime which while not elegant vb - will work. Hope ths will be of help to other people who have come up against the moral high ground.

Sub clean()
For r = Range("A65536").End(xlUp).Row To 2 Step -1
If Cells(r, 1).Value <> Cells(r - 1, 1).Value Then
Rows(r).Insert Shift:=xlDown
End If
Next r
End Sub

 

[SkipVought]

Use a helper column to number the rows of data.

Copy the column with the row numbers to the botttom of the table

paste the number is BLANK rows immediately below the data

Sort on the helper column to intersperse blank rows.

Skip,

_[red][/red]