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

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

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

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

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

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