Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, copy lines with specific value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, copy lines with specific value
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) "Robert" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, copy lines with specific value
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, copy lines with specific value
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) "Robert" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to modify specific lines in a .ini file? | Excel Discussion (Misc queries) | |||
printing specific lines | Excel Discussion (Misc queries) | |||
How do I get page down to move a specific # of lines in Excel? | Excel Worksheet Functions | |||
Detect lines in specific sheet at load time | Excel Programming | |||
Print only lines that have a value in a specific colum | Setting up and Configuration of Excel |