If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
separate lines in excel
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 |
#2
|
|||
|
|||
separate lines in excel
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 |
#3
|
|||
|
|||
separate lines in excel
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 |
#4
|
|||
|
|||
separate lines in excel
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 |
#5
|
|||
|
|||
separate lines in excel
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 |
Thread Tools | |
Display Modes | |
|
|