Insert Row

[Connie Martin]

I have this macro, which I got from some guru in this database a few years
ago, which has always worked, and but won't work in the spreadsheet I'm
working on today. The macro inserts a blank row wherever there's a change in
whatever column you select. The column I'm selecting has data that sometimes
starts with a number, sometimes with a symbol, sometimes with a letter. Is
it because of that that this macro won't work?

Sub InsertRow_At_Change()
Dim i As Long
Dim colno As Long
colno = InputBox("Enter a Column Number")
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
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, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

 

[Gord Dibben]

Shouldn't matter.

I just tested in 2003 with numbers, letters and symbols entered using Alt + xxxx

Worked fine.

What type of symbols are you speaking of?


Gord Dibben MS Excel MVP

 

[Connie Martin]

Only the # sign. I am using 2003, as well. It won't work. The hour glass
is on the screen for a little bit, then you see a flicker in the rows and
that's it. Nothing happens. Connie

 

[Gord Dibben]

Re-tested with this data(simplified for this post)

a
a
#
#
1
1
b
b


Got an inserted row at each change in data.

a
a

#
#

1
1

b
b


You can send me the file via email if you wish.

Change the AT and DOT for my address.


Gord

 

[Connie Martin]

Sure. That would be great. Thank you for the offer. I have just sent it.
Connie

 

[Gord Dibben]

Received the workbook and made some revisions.

Mainly moved the macro code out of a sheet module into a General module.

Code in sheet modules is great for events and Control Toolbox Command Buttons
but doesn't work well with buttons from the Forms Toolbar or running from
Tools>Macro>Macros.


Gord