PDA

View Full Version : Solved: Copy rows selectively



realitybend
07-14-2008, 11:48 AM
I need some vba that will copy a row in "Sheet1" if it has a valid number in column B.

Any suggestions?

Thanks

mdmackillop
07-14-2008, 12:22 PM
Sub Test()
Dim cel As Range
With ActiveSheet
For Each cel In Intersect(.Columns(2), .UsedRange)
If IsNumeric(cel) Then
cel.EntireRow.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next
End With
End Sub

realitybend
07-14-2008, 01:27 PM
Wow. Thanks.

I have a couple more things I need it to do:
It should only copy cells from row 3 or below.

Also, I need it to look at the numbers in column A for the row it's copying, and if there's an identical number in column A of Sheet2, it needs to overwrite it.

mdmackillop
07-14-2008, 01:50 PM
Try

Option Explicit
Sub Test()
Dim cel As Range, Fnd As Range, colA As Single
With ActiveSheet
For Each cel In Intersect(.Columns(2), .UsedRange)
If IsNumeric(cel) and cel.Row > 3 Then
colA = cel.Offset(, -1)
Set Fnd = Sheets(2).Columns(1).Find(colA, LookIn:=xlValues, lookat:=xlWhole)
If Not Fnd Is Nothing Then
cel.EntireRow.Copy Fnd
Else
cel.EntireRow.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
End If
Next
End With
End Sub

realitybend
07-15-2008, 10:49 AM
Awesome!

Thanks so much.
:bow:

realitybend
07-21-2008, 09:17 AM
Pardon my ignorance, but why is it "Offset(, -1)" that and not "Offset(0, -1)"?

mdmackillop
07-21-2008, 09:58 AM
You can use default values in code, where the default here is 0

Similar to
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
You can use
Find(What, , , xlwhole)
to reduce typing

realitybend
07-21-2008, 10:11 AM
Thanks.