Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Rows
I am trying to insert rows into a spreadsheet. I was given the following
macro to accomplish that: Sub ABC() Dim lastrow as Long, i as Long set lastrow = cells(rows.count,1).End(xlup).Row for i = lastrow to 2 step -1 if cells(i,1) < cells(i-1,1) then rows(i).Insert end if Next End Sub When I ran this sub it errors at "set lastrow =" saying there is a Compile Error. If I run it from excel, a msg box says "No cells Found". Tom O had me remove the "set lastrow" line, but now nothing happens when I run the macro. I replied to the thread yesterday, but I haven't gotten a response. Anyone know what I can do? What I am trying to do is add a row after various groups of customer numbers. The number of similar customer numbers will change each time I run the report. This is an example of what I have now: 1 1 2 3 3 3 4 5 5 5 5 This is what I what to get as a result of the macro: 1 1 2 3 3 3 4 5 5 5 5 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Rows
No one said to remove the line. Dave Peterson said to remove "set"
See the revised code below: Sub ABC() Dim lastrow as Long, i as Long lastrow = cells(rows.count,1).End(xlup).Row for i = lastrow to 2 step -1 if cells(i,1) < cells(i-1,1) then rows(i).Insert end if Next End Sub -- Regards, Tom Ogilvy "Josh O." wrote in message ... I am trying to insert rows into a spreadsheet. I was given the following macro to accomplish that: Sub ABC() Dim lastrow as Long, i as Long set lastrow = cells(rows.count,1).End(xlup).Row for i = lastrow to 2 step -1 if cells(i,1) < cells(i-1,1) then rows(i).Insert end if Next End Sub When I ran this sub it errors at "set lastrow =" saying there is a Compile Error. If I run it from excel, a msg box says "No cells Found". Tom O had me remove the "set lastrow" line, but now nothing happens when I run the macro. I replied to the thread yesterday, but I haven't gotten a response. Anyone know what I can do? What I am trying to do is add a row after various groups of customer numbers. The number of similar customer numbers will change each time I run the report. This is an example of what I have now: 1 1 2 3 3 3 4 5 5 5 5 This is what I what to get as a result of the macro: 1 1 2 3 3 3 4 5 5 5 5 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Rows
Hi,
Remove "Set" from your lastrow statement. lastrow = cells(rows.count,1).End(xlup).Row "Josh O." wrote: I am trying to insert rows into a spreadsheet. I was given the following macro to accomplish that: Sub ABC() Dim lastrow as Long, i as Long set lastrow = cells(rows.count,1).End(xlup).Row for i = lastrow to 2 step -1 if cells(i,1) < cells(i-1,1) then rows(i).Insert end if Next End Sub When I ran this sub it errors at "set lastrow =" saying there is a Compile Error. If I run it from excel, a msg box says "No cells Found". Tom O had me remove the "set lastrow" line, but now nothing happens when I run the macro. I replied to the thread yesterday, but I haven't gotten a response. Anyone know what I can do? What I am trying to do is add a row after various groups of customer numbers. The number of similar customer numbers will change each time I run the report. This is an example of what I have now: 1 1 2 3 3 3 4 5 5 5 5 This is what I what to get as a result of the macro: 1 1 2 3 3 3 4 5 5 5 5 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Rows
That looks like some of Tom's code... Normally he does not miss much but in
this case... delete the word set from the beginning of set lastrow = cells(rows.count,1).End(xlup).Row and you should be off to the races... HTH "Josh O." wrote: I am trying to insert rows into a spreadsheet. I was given the following macro to accomplish that: Sub ABC() Dim lastrow as Long, i as Long set lastrow = cells(rows.count,1).End(xlup).Row for i = lastrow to 2 step -1 if cells(i,1) < cells(i-1,1) then rows(i).Insert end if Next End Sub When I ran this sub it errors at "set lastrow =" saying there is a Compile Error. If I run it from excel, a msg box says "No cells Found". Tom O had me remove the "set lastrow" line, but now nothing happens when I run the macro. I replied to the thread yesterday, but I haven't gotten a response. Anyone know what I can do? What I am trying to do is add a row after various groups of customer numbers. The number of similar customer numbers will change each time I run the report. This is an example of what I have now: 1 1 2 3 3 3 4 5 5 5 5 This is what I what to get as a result of the macro: 1 1 2 3 3 3 4 5 5 5 5 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Rows
Sorry Tom. I am losing my mind. I know you don't miss much. The macro is
working fine now. I working on something else when I got that reply, and I read it too quickly. Thanks for all your help. "Josh O." wrote: I am trying to insert rows into a spreadsheet. I was given the following macro to accomplish that: Sub ABC() Dim lastrow as Long, i as Long set lastrow = cells(rows.count,1).End(xlup).Row for i = lastrow to 2 step -1 if cells(i,1) < cells(i-1,1) then rows(i).Insert end if Next End Sub When I ran this sub it errors at "set lastrow =" saying there is a Compile Error. If I run it from excel, a msg box says "No cells Found". Tom O had me remove the "set lastrow" line, but now nothing happens when I run the macro. I replied to the thread yesterday, but I haven't gotten a response. Anyone know what I can do? What I am trying to do is add a row after various groups of customer numbers. The number of similar customer numbers will change each time I run the report. This is an example of what I have now: 1 1 2 3 3 3 4 5 5 5 5 This is what I what to get as a result of the macro: 1 1 2 3 3 3 4 5 5 5 5 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Rows
Josh
You were told by Dave Peterson to change the set lastrow line to lastrow = cells(rows.count,1).End(xlup).Row Works for me. Gord Dibben Excel MVP On Tue, 10 May 2005 09:12:13 -0700, "Josh O." wrote: I am trying to insert rows into a spreadsheet. I was given the following macro to accomplish that: Sub ABC() Dim lastrow as Long, i as Long set lastrow = cells(rows.count,1).End(xlup).Row for i = lastrow to 2 step -1 if cells(i,1) < cells(i-1,1) then rows(i).Insert end if Next End Sub When I ran this sub it errors at "set lastrow =" saying there is a Compile Error. If I run it from excel, a msg box says "No cells Found". Tom O had me remove the "set lastrow" line, but now nothing happens when I run the macro. I replied to the thread yesterday, but I haven't gotten a response. Anyone know what I can do? What I am trying to do is add a row after various groups of customer numbers. The number of similar customer numbers will change each time I run the report. This is an example of what I have now: 1 1 2 3 3 3 4 5 5 5 5 This is what I what to get as a result of the macro: 1 1 2 3 3 3 4 5 5 5 5 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i insert blank rows between data that is thousands of rows | Excel Discussion (Misc queries) | |||
Insert rows: Formats & formulas extended to additonal rows | Excel Worksheet Functions | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
How do i insert of spacer rows between rows in large spreadsheets | Excel Discussion (Misc queries) | |||
Copy Rows and insert these rows before a page break | Excel Programming |