Adding rows

[Guest]

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

 

[carlo]

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

 

[Guest]

In the meantime, would you mind giving me the VBA solution?

I appreciate your assistance.

 

[carlo]

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

 

[Guest]

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]

Hi Guy

If you want it to work in all your workbooks, you better change it to
an add-in.
I never did that though, but at least i can try:

For that, open a new workbook
hit alt+F11
Insert -> Module.
(Change the name of the Module in the properties window, if you want)
Paste the Macro in this module
Open the Worksheet in the VBA-editor
(if you don't see it press Ctrl+R)
Insert following code:
'-------------------------------------------------------------------------------------------------
Private Sub Workbook_AddinInstall()

Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

On Error Resume Next

Application.CommandBars("Worksheet Menu Bar").Controls("My
Tools").Delete

On Error GoTo 0

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
For Each mb In Application.CommandBars(1).Controls
If mb.ID = 30010 Then
iHelpMenu = mb.Index
Exit For
End If
Next mb

Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Before:=iHelpMenu)
cbcCutomMenu.Caption = "My Tools"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert rows in Column A"
.OnAction = "ins_rows"
End With

End Sub

Private Sub Workbook_AddinUninstall()

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("My
Tools").Delete

End Sub
'-------------------------------------------------------------------------------------------------
close the vba-editor

save the excel sheet as .xla
(it should automatically be saved to your add-in folder)

Close Excel and open a new worksheet.
Tools -> Add-Ins
and you should see your add-in in the list
(the name with which you saved it without .xla)

hope that helps, please test it and give feedback as it is my
first try with add-ins.

if you want to have additional infos, check google, there
are some great tutorials like that from ozgrid:
http://www.ozgrid.com/VBA/excel-add-in-create.htm

that takes care of your question 1 and 2.

now to question 3:
it's a fairly simple code, which checks if the value
of this rows cell in column A is like the one stored in
var_before. To read it more easily copy the whole
code and paste it into the VBA-editor.
'---------------------------------------------------------------------------------
Sub ins_rows()

Dim var_before As Variant
'set var_before to cell A1 (row 1, column 1)
'Cells(1,1).value means the value of Cell A1
var_before = Cells(1, 1).Value

For i = 1 To 65536
'this function will only go as far as the first
'blank cell in column A
If Cells(i, 1).Value = "" Then
Exit For
End If
'if the cell value is not the same as the one
'stored in var_before do following:
If var_before <> Cells(i, 1).Value Then
'set var_before = cell value so that the next
'comparison will be accurate
var_before = Cells(i, 1).Value
'insert 1 row where the row(i) is right now,
'move all the other rows down.
Rows(i).Insert Shift:=xlDown
'jump over one line, because we inserted one
i = i + 1
End If

Next i

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

if there are any other questions, just ask

Cheers Carlo

 

[Guest]

It seems to work. I have no idea what I'm doing so that makes me uneasy.

Two lines in the Addininstall code and one in the Adduninstall code are red:

Application.CommandBars("Worksheet Menu Bar").Controls("My

and

Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Before:=iHelpMenu)

and

Application.CommandBars("Worksheet Menu Bar").Controls("My

It doesn't seem to be affecting anything.

Thank you so much for your help. It does what it has to do!

Guy

 

[carlo]

It seems to work. I have no idea what I'm doing so that makes me uneasy.

Two lines in the Addininstall code and one in the Adduninstall code are red:

Application.CommandBars("Worksheet Menu Bar").Controls("My

and

Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Before:=iHelpMenu)

and

Application.CommandBars("Worksheet Menu Bar").Controls("My

It doesn't seem to be affecting anything.

Thank you so much for your help. It does what it has to do!

Guy






























- Show quoted text -

Hi Guy

Happy that it works.

But I'm not to happy with those redlines you told me!
the problem is probably the wordwrap of the discussion group.
Try to put the lines together, for example:
Application.CommandBars("Worksheet Menu Bar").Controls("My
is definitely not finished (you maybe know that problem from
formulas ) so try to take the line below up, be careful it should
be "My Tools" not "MyTools".

I didn't want to post a detailled description because i didn't know
which parts you would understand. But if you have any questions
just go ahead and ask.

cheers Carlo

 

[Guest]

You are correct about the red being caused by the wrap. All is well now.

Thanks so much. I cannot ask you for further clarification because I need a
course in VBA. Right now I am too ignorant to ask questions. That is why I
wanted a non-VBA solution.

You are very gracious to expend so much effort on my behalf and I appreciate
it. Happy Thanksgiving!

 

[carlo]

Ok, then everyone is happy (except maybe the turkey )

if you still have an answer, just post it.

Happy Thanksgiving to you too

Carlo