Copy range if column N is empty
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
.
|