Copy entire row if....

B

Biff

Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking for
uniques. I'm using a helper column (AE) in Sheet2 that identifies the
uniques by returning the row number if it's a unique or a formula blank ("")
if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have that
ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new sheet
are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy that
ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff
 
N

Norman Jones

Hi Biff,

Try:
'==========>>
Public Sub TestIt()
Dim srcRng As Range
Dim destRng As Range

Set destRng = Sheets("Sheet3").Range("A2")

On Error Resume Next
Set srcRng = Sheets("Sheet2").Columns("AE"). _
SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If Not srcRng Is Nothing Then
srcRng.Copy Destination:=destRng
End If

End Sub
'<<==========
 
R

Rowan Drummond

Hi Biff

Try:

Sub test()
Dim eRow As Long
Dim i As Long
Dim CpyRow As Long

CpyRow = 2
With Sheets("Sheet2")
eRow = .Cells(Rows.Count, "AE").End(xlUp).Row
For i = 2 To eRow 'starts in row 2 assuming headings in row 1
If .Cells(i, "AE").Value <> "" Then
.Rows(i).Copy Sheets("Sheet3").Cells(CpyRow, 1)
CpyRow = CpyRow + 1
End If
Next i
End With
End Sub

Hope this helps
Rowan
 
B

Biff

Hi Norman!

That only copies the numeric values from column AE. I need the ENTIRE ROW
copied.

Well, not the ENTIRE row but the range An:AAn.

Thanks!

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can I send an email too? 3
Array with many cells from a row but not in order or together 11
loop to copy paste 5
Copy row info but not in normal column sequence 6
Copy .Areas.Item(1,2,3,4,5, etc.) to a column or a row 5
Create if partial match then copy and paste data from one cell to another 1
Read and search 2
help with syntax for copy and pasting rows across sheets 4

Top