Insert Row
 

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

Insert Row
 

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

On Tue, 13 May 2008 11:08:01 -0700, Connie Martin
wrote:

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

Insert Row
 

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" wrote:

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

On Tue, 13 May 2008 11:08:01 -0700, Connie Martin
wrote:

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

Insert Row
 

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

On Tue, 13 May 2008 12:02:02 -0700, Connie Martin
wrote:

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" wrote:

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

On Tue, 13 May 2008 11:08:01 -0700, Connie Martin
wrote:

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

Insert Row
 

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

"Gord Dibben" wrote:

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

On Tue, 13 May 2008 12:02:02 -0700, Connie Martin
wrote:

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" wrote:

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

On Tue, 13 May 2008 11:08:01 -0700, Connie Martin
wrote:

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

Insert Row
 

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
ToolsMacroMacros.


Gord


On Tue, 13 May 2008 13:18:01 -0700, Connie Martin
wrote:

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

"Gord Dibben" wrote:

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

On Tue, 13 May 2008 12:02:02 -0700, Connie Martin
wrote:

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" wrote:

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

On Tue, 13 May 2008 11:08:01 -0700, Connie Martin
wrote:

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