Insert Row

  • Thread starter Thread starter Connie Martin
  • Start date Start date
C

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
 
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
 
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
 
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
 
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
 
Back
Top