I posted this in this group on saturday.

The first half is my question.

The 2nd half is the response.

My question today...

What do I do with it. I think it goes into the visual basic editor but I
have been wrong before. Thats why I am here.

If that is the case how do I run it. If not the case what do I do with it.

Can I copy and paste it?

Thanks

Question
Here is a problem I have:
123456789
134567890
135678901
1456789012
157890123
this group of numbers is in my Sheet and each line is in a row by itself and
they are in 9 different columns. for brevity I just used 9 numbers for each
line. I need to know if there is a way to auto insert a row between each
individual number. My real SS will have about 900 rows with 60 characters
per row that I have already performed text to columns on. There Can be as
few as 1 row per number or as many as 25 rows per number. in the sample
above the second number would be the dividing line Please help


Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
If Cells(X, 1).Value < "" Then
If Cells(X - 1, 1).Value < "" Then
Cells(X, 1).entirerow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

f you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the code provided by Gord.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()


--
Jacob (MVP - Excel)


"Kevin" wrote:

I posted this in this group on saturday.

The first half is my question.

The 2nd half is the response.

My question today...

What do I do with it. I think it goes into the visual basic editor but I
have been wrong before. Thats why I am here.

If that is the case how do I run it. If not the case what do I do with it.

Can I copy and paste it?

Thanks

Question
Here is a problem I have:
123456789
134567890
135678901
1456789012
157890123
this group of numbers is in my Sheet and each line is in a row by itself and
they are in 9 different columns. for brevity I just used 9 numbers for each
line. I need to know if there is a way to auto insert a row between each
individual number. My real SS will have about 900 rows with 60 characters
per row that I have already performed text to columns on. There Can be as
few as 1 row per number or as many as 25 rows per number. in the sample
above the second number would be the dividing line Please help


Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
If Cells(X, 1).Value < "" Then
If Cells(X - 1, 1).Value < "" Then
Cells(X, 1).entirerow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

I am getting a COMPILE SYNTAX ERROR With the second line (sandy MANN)
highlighted

"Jacob Skaria" wrote:

f you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the code provided by Gord.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()


--
Jacob (MVP - Excel)


"Kevin" wrote:

I posted this in this group on saturday.

The first half is my question.

The 2nd half is the response.

My question today...

What do I do with it. I think it goes into the visual basic editor but I
have been wrong before. Thats why I am here.

If that is the case how do I run it. If not the case what do I do with it.

Can I copy and paste it?

Thanks

Question
Here is a problem I have:
123456789
134567890
135678901
1456789012
157890123
this group of numbers is in my Sheet and each line is in a row by itself and
they are in 9 different columns. for brevity I just used 9 numbers for each
line. I need to know if there is a way to auto insert a row between each
individual number. My real SS will have about 900 rows with 60 characters
per row that I have already performed text to columns on. There Can be as
few as 1 row per number or as many as 25 rows per number. in the sample
above the second number would be the dividing line Please help


Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
If Cells(X, 1).Value < "" Then
If Cells(X - 1, 1).Value < "" Then
Cells(X, 1).entirerow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

You can either remove that line or remark that line by using a ' apostrophe
in front...(which is already there).....

You may also try the below ones which does the same with values at ColA

Sub InsertBlankRowsbetweenChangingValues()
Dim lngRow As Long
Application.ScreenUpdating = False
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) < "" And Range("A" & lngRow - 1) < "" And _
Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).Insert
Next
Application.ScreenUpdating = True
End Sub

--
Jacob (MVP - Excel)


"Kevin" wrote:

I am getting a COMPILE SYNTAX ERROR With the second line (sandy MANN)
highlighted

"Jacob Skaria" wrote:

f you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the code provided by Gord.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()


--
Jacob (MVP - Excel)


"Kevin" wrote:

I posted this in this group on saturday.

The first half is my question.

The 2nd half is the response.

My question today...

What do I do with it. I think it goes into the visual basic editor but I
have been wrong before. Thats why I am here.

If that is the case how do I run it. If not the case what do I do with it.

Can I copy and paste it?

Thanks

Question
Here is a problem I have:
123456789
134567890
135678901
1456789012
157890123
this group of numbers is in my Sheet and each line is in a row by itself and
they are in 9 different columns. for brevity I just used 9 numbers for each
line. I need to know if there is a way to auto insert a row between each
individual number. My real SS will have about 900 rows with 60 characters
per row that I have already performed text to columns on. There Can be as
few as 1 row per number or as many as 25 rows per number. in the sample
above the second number would be the dividing line Please help


Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
If Cells(X, 1).Value < "" Then
If Cells(X - 1, 1).Value < "" Then
Cells(X, 1).entirerow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

I posted the macro with an apsotrophe before the Sandy Mann line.

That makes it a comment.

You must have removed the apostrophe.

Either add it back or just delete the line entyirely.


Gord

On Wed, 12 May 2010 05:42:01 -0700, Kevin
wrote:

I am getting a COMPILE SYNTAX ERROR With the second line (sandy MANN)
highlighted

"Jacob Skaria" wrote:

f you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the code provided by Gord.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()


--
Jacob (MVP - Excel)


"Kevin" wrote:

I posted this in this group on saturday.

The first half is my question.

The 2nd half is the response.

My question today...

What do I do with it. I think it goes into the visual basic editor but I
have been wrong before. Thats why I am here.

If that is the case how do I run it. If not the case what do I do with it.

Can I copy and paste it?

Thanks

Question
Here is a problem I have:
123456789
134567890
135678901
1456789012
157890123
this group of numbers is in my Sheet and each line is in a row by itself and
they are in 9 different columns. for brevity I just used 9 numbers for each
line. I need to know if there is a way to auto insert a row between each
individual number. My real SS will have about 900 rows with 60 characters
per row that I have already performed text to columns on. There Can be as
few as 1 row per number or as many as 25 rows per number. in the sample
above the second number would be the dividing line Please help


Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
If Cells(X, 1).Value < "" Then
If Cells(X - 1, 1).Value < "" Then
Cells(X, 1).entirerow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP