VBA, copy lines with specific value

R

Robert

Hi all,

Can someone advice me which VBA statement to use for substracting a
range of lines that match with for example the value in column A, and
subsequently copy them (whole line) to an empty sheet (say "Sheet2")


Lookup value in column A: CCC

A B C
1 AAA 123 111
2 CCC 345 222
3 BBB 678 333
4 CCC 912 444

Result: line 2 & 4


Any suggestions is mostly appreciated.

Thanks a lot!

Rgds,
Robert
 
B

Bob Phillips

With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i,"A").Value = "CCC" Then
iNextRow = iNextRow + 1
.Rows(i).Copy Worksheets("Sheet2").Cells(INextrRow,"A")
End If
Next i
End With

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

Robert

Hi Bob,

Thanks a lot for the promt assistance!
When running the statement I get an error for .Rows(i).Copy
Worksheets("Sheet2").Cells(INextrRow,"A")
Can it be that you have to include some sort of offset function to
paste the first line in say A1 and for the following lines something
like End(xldown).Offset(1, 0).Select?
Thanks again
Rgds,
Robert
 
B

Bob Phillips

No, there was a typo in my code. This is what it should say

With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "CCC" Then
iNextRow = iNextRow + 1
.Rows(i).Copy Worksheets("Sheet2").Cells(iNextRow, "A")
End If
Next i
End With


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

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

Top