[Dreambringer]

Ok not sure if this can be done or not, but hopefully it can.


What I want to create is a macro or something that would read the values in column A, if it finds a duplicate number it would insert a row under it

i.e.

5
5
6
7
8


It would insert a row between the 5 and 6.


Is this possible?

[cristobal03]

Yep. Possible. But I'm not sure how to do it.

Here's an important point you didn't mention and your example doesn't address: there's a difference between duplicity and multiplicity. I imagine you'd want to insert a row under the last row in the set (e.g., if you had three 5s in your example, you'd want a row under the third 5 not the second 5).

So, you'd go through the Range A:A, and check the value of a cell. If the next row's value matched, you'd enter a loop that kept going until the value didn't match the next row. At that point you'd insert a row.

All of this can be done. Zack or Andy or OBP or a lot of other users'll likely post something (mentioned those three because I always see them posting code in Excel threads).

GL

chris.

[Dreambringer]

yep you are right, I just have no idea how to do it, or even where to start

[cristobal03]

I'd really love to try it, to see if I can, but I'm out of time for today. If nobody posts back before Monday (doubtful), I'll give it a shot then.

chris.

[Glaswegian]

Hi

Try this

Sub Splitup()

Dim Rng As Range
Dim x As Long
Set Rng = Range("A1:A" & Range("A65536").End(xlUp).row)
For x = Rng.Rows.Count To 2 Step -1
If Rng.Cells(x, 1).Offset(-1, 0).Value <> Rng.Cells(x, 1).Value Then
Rng.Cells(x, 1).EntireRow.Insert Shift:=xlDown

End If
Next x

End Sub

Regards

[Zack Barresse]

While I like Glaswegian's routine, and have used it's likes on many occasions, this line could be changed ...

Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row)

In Excel 12 (not yet released as of this time) is going to increase the number of rows and columns it has to over a billion cells. It was always requested by a large amount of people. If we are to advance with our versions of Excel and have our code be forward compatible, we need to prepare for this. Seing as how there will be more than 65,536 rows, it would not be the safest to make all of our programs this way. If we don't change it then we will make some freelance programmers very happy, or some IT person very unhappy with the amount of code they'll have to change.

Instead we can use ...

Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

This will work in current versions and in future versions.

Okay, getting off soap box now.

[cristobal03]

Sorry, trying to work through Glaswegian's code...seems like it'd insert a row at every point in the range where the values between the active cell and the cell above it were different. I was thinking, OP wanted code that would only insert rows at points in the range where there were sets of identical cells. That is, using OP's example, wouldn't Glaswegian's code make

5
5
new row
6
new row
7
new row
8

I thought OP wanted something more like

5
5
new row
6
7
8
9
9
9
new row
10

Maybe this was a misunderstanding on my part?

chris.

[bomb #21]

Originally Posted by cristobal03

I thought OP wanted something more like ... Maybe this was a misunderstanding on my part?

If it wasn't then it's a good spot.

Here's a brute force version (since I don't do proper code) for what you're thinking (list starts in A1 & contains no blanks):

Sub Macro1()
Range("A1").Offset(WorksheetFunction.CountA(Range("A:A")), 0) = "X"
Range("A2").Select
Do Until ActiveCell = "X"
If ActiveCell = ActiveCell.Offset(-1, 0) Then
If ActiveCell <> ActiveCell.Offset(1, 0) Then
ActiveCell.Offset(1, 0).Insert Shift:=xlDown
End If
Else
End If
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell = ""
End Sub

[Zack Barresse]

Generally, when coding in VBA, especially with Excel, there is little reason to use .Select or .Activate as it only makes your code more inefficient. It does this because it uses memory, more than it would than if you just took care of it in code instead of *physically* moving the 'cursor'.

Glaswegian has done this as he iterates through his set range via integer increase/decrease and utilizes that in a row manipulation. This doesn't work too well with multiple columns/rows to iterate through though, other techniques would be better suited for something like that.

Basically, it all comes down to what works best for each specific situation. There are many ways to skin a cat, and I'm not saying bomb #21 is wrong by any means. I am only saying that there are more efficient ways and personal preference.

[bomb #21]

Originally Posted by firefytr

Generally, when coding in VBA, especially with Excel, there is little reason to use .Select or .Activate as it only makes your code more inefficient.

Yup, that's what I usually say. Exceptionally I went for Do ... Loop because there's no way I can figure how many rows the code will add before it actually runs (i.e. how many non-unique #s). Fancy the challenge Zack?

[Zack Barresse]

Oh, I always fancy a challenge mate.

Just waiting to hear back a definitive answer from the OP regarding Chris' question/solution and if this solves it for him/her.

[Glaswegian]

Think I'll just stay out of this and let you two fight over it.

(Hi Zack!)

[Dreambringer]

Actually either way would work, I have not given it a try yet, but will let you know. Thanx a billion for your suggestions and help!

[bomb #21]

Originally Posted by Glaswegian

Think I'll just stay out of this and let you two fight over it.

Hi Glas. There's no needle between Zack and I AFAIK. For my part, the guy's awesome -- he's picked up more in the last year or two than I've managed in the last decade.

[Glaswegian]

Originally Posted by bomb #21

Hi Glas. There's no needle between Zack and I AFAIK. For my part, the guy's awesome -- he's picked up more in the last year or two than I've managed in the last decade.

Oh I know that Andy. I just thought I'd stay out the way and let you two heavyweights fight over my code!!