Hi Alan,
Rick and I aren't competing; he's the veteran, I'm the newbie. That
said, I like his coding and aspire to be as good as he is some day, and
so the competition is with myself and my VB[A} skills.

<<How do I clear rows with an entry in column N?
What do you mean by 'clear rows'? Do you mean ClearContents or Delete?
Do you want to do it during the MoveCarryOvers routine OR after that
routine is done. (During will be a bit tricky if deleting rows)

Basically it would involve repeating the process with ClearContents OR
EntireRow.Delete

Garry
--


It happens that ajm1949 formulated :
Hello Rick and Garry
Seems like you guys are a bit competitive...Keep up the great work. I am
also learning a lot from all the posts here. The first code sample had a
minor error.
Sub MoveCarryOvers()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr1 As Long, rng As Range
Dim c As Object
Set sh1 = Sheets("Sales")
Set sh2 = Sheets("Carry overs")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("N1:N" & lr1)
For Each c In rng
If c.Value = "" Then
Range("A" & c.Row).Resize(1, 21).Copy _
sh2.Range("A" & sh2.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row)
End If
Next
End Sub

Rick's sample definitely runs noticably faster. Thank Rick.

My next question is this.

How do I clear rows with an entry in column N?

Cheers
Alan

"GS" wrote:

Rick Rothstein was thinking very hard :
Here is a considerably shorter (and I believe much faster) macro which does
what your macro does...

Sub terranean()
On Error Resume Next
Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _
Columns("N").SpecialCells(xlCellTypeBlanks). _
EntireRow).Copy Sheets("Carryovers").Range("A2")
End Sub

--
Rick (MVP - Excel)



"JLGWhiz" wrote in message
...
This might work

Sub terrain()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr1 As Long, lr2 As Long, rng As Range
Set sh1 = Sheets("Sales")
Set sh2 = Sheets("Carryovers")
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("N1:N" & lr)
For Each c In rng
If c.Value = "" Then
Range("A" & c.Row).Resize(1, 21).Copy _
sh2.Range("A" & sh2.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row)
End If
Next
End Sub




"ajm1949" wrote in message
...
Hi all
I have looked through the forums but can't find what i need.

In each row that column N (Sheet name is Sales) is empty I want to copy
columns A:T to another worksheet (Sheet Name is Carryovers) starting at
A2 Hope this is clear enough

many thanks in advance

Alan
ajm1949



Now, Rick, I thought I was going to beat you to this one. Not surprised
I didn't, ..just a bit disappointed in myself for not getting my reply
finalized sooner. (It's been a distracting day today)

Anyway, I just thought I'd let you know that I'm learning a lot from
your posts, and so my disappoinment is offset by the discovery that I
was about to duplicate what you posted. Credit goes to you for that!
Thanks for all the good learning content that you post!

regards,
Garry


.