It works beautifully but...

all I did was copy and paste into a VBA window and then click Run.

1) How do I save this so I can run it in any workbook I open?

2) How do I create a keyboard shortcut or button on a toolbar to run it?

3) Do you mind briefly explaining exactly what this code does (besides
solving my problem!)? Where it says OK I do not need an explanation.

Sub ins_rows() OK

Dim var_before As Variant OK
var_before = Cells(1, 1).Value What is Cells(1, 1).Value?

For i = 1 To 65536 OK
If Cells(i, 1).Value = "" Then I'm not sure what Cells(i,
1).Value is.
Exit For OK
End If OK
If var_before < Cells(i, 1).Value Then OK
var_before = Cells(i, 1).Value Huh? If the variant is
not = then it is =?
Rows(i).Insert Shift:=xlDown ??
i = i + 1 OK
End If OK

Next i OK

End Sub OK

Any help will be appreciated. I said I didn't know VBA.... : (

Happy Thanksgiving!

"carlo" wrote:

Hy Guy

here's a small VBA Sub:
'----------------------------------------------------------------------------
Sub ins_rows()

Dim var_before As Variant
var_before = Cells(1, 1).Value

For i = 1 To 65536
If Cells(i, 1).Value = "" Then
Exit For
End If
If var_before < Cells(i, 1).Value Then
var_before = Cells(i, 1).Value
Rows(i).Insert Shift:=xlDown
i = i + 1
End If

Next i

End Sub
'----------------------------------------------------------------------------

i think you know how to put it in the VBA-editor, but
if not, just ask and i will try to explain it to you.
Also if you want further explanation to the code.

hth

Carlo

On Nov 22, 12:11 am, Guy Lydig
wrote:
In the meantime, would you mind giving me the VBA solution?

I appreciate your assistance.



"carlo" wrote:
Hi Guy

in my opinion it depends on the size of your data.
If you have to do this often, and the data is large
i would recommend a small VBA-function.

Maybe someone else can help you, with a non-VB
approach.

Otherwise if you need help with VBA, just ask.

Cheers
Carlo

On Nov 21, 4:03 pm, Guy Lydig
wrote:
Excel XP

Column A has record numbers: some repeat; some do not. Columns B through K
contain other data. Sheet is sorted by Column A. I want to add a blank row
before each new number.

Example: I have

1
1
2
2
2
3

I want:

1
1

2
2
2

3

I can do this using subtotals to count and then delete the contents of the
count rows with helper columns.

I can do this by using Countif in a helper column to number each entry and
then using Advanced Filter to add each record number one more time to the end
of column A. I then add a number greater than the largest result of the
Countif (e.g.: 1000) to all the newly added record numbers and re-sort. Then
I have to delete the text in the rows with "1000."

Is there a simpler way to do this? I would prefer not to use VB as I am
totally unfamiliar with it.

Thanks- Hide quoted text -

- Show quoted text -