Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
Hi,
I have a db that i want to to extract some lines to other sheet when a word is typed. I give example in below. When "K" is typed i Want that line 1,4,5 to be extracted to other sheet by order. How do i do this? A B C 1 Day K 2 Day W 3 Day Y 4 Day K 5 Day K 6 Day Y |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"C").Value = "K" Then iRow = iRow + 1 Rows(i).Copy Worksheets("Sheet2").Rows(iRow) End If Next i -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Micos3" wrote in message ... Hi, I have a db that i want to to extract some lines to other sheet when a word is typed. I give example in below. When "K" is typed i Want that line 1,4,5 to be extracted to other sheet by order. How do i do this? A B C 1 Day K 2 Day W 3 Day Y 4 Day K 5 Day K 6 Day Y |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
I't doesn't worked for me, i'm a newbie in VBA, so i'll tell what i did, i
putted the formula in workbook, and in active sheet, but it didn't happened nothing in sheet2, like formula says. What did i do wrong? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
I would wrap it as a single macro
Sub myData() For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row If Cells(i,"C").Value = "K" Then iRow = iRow + 1 Rows(i).Copy Worksheets("Sheet2").Rows(iRow) End If Next i End Sub put it in a standard code module, and then call the macro from the worksheet via ToolsMacroMacros... -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Micos3" wrote in message ... I't doesn't worked for me, i'm a newbie in VBA, so i'll tell what i did, i putted the formula in workbook, and in active sheet, but it didn't happened nothing in sheet2, like formula says. What did i do wrong? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
Many thanks cos with instructions so detailed even a newbie like me could
made that. The more incredible is that is the first time VBA worked for me, i guess this says all :D I only ask 2 things: Is it possible to choose not the whole line but some rows? I have to do the same for 2 more sheets, like K, but with other letter, i will apply this macro, offcourse changing the variables, can i connect the same button to 3 diferent macros? Once again, Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
I forget to ask 1 more, can i put the data not to begin in i1st line but in
2nd? Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
You could setup the test value in a cell and check that. With starting at
row 2 Sub myData() For i = 2 To Cells(Rows.Count,"A").End(xlUp).Row If Cells(i,"C").Value = Range("A1").Value Then iRow = iRow + 1 Rows(i).Copy Worksheets("Sheet2").Rows(iRow) End If Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Micos3" wrote in message ... I forget to ask 1 more, can i put the data not to begin in i1st line but in 2nd? Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
Isn't working, i don't know why, but i'll gonna try with a new db and macro
now with 3 sheet, after lunch. Other thing, Is it possible to choose not the whole line but some just some columns? Thanks again |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
Should be
Sub myData() For i = 2 To Cells(Rows.Count,"A").End(xlUp).Row If Cells(i,"C").Value = Range("A1").Value Then iRow = iRow + 1 Cells(i,"C").Resize(,10).Copy Worksheets("Sheet2").Range("A" & iRow) End If Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Micos3" wrote in message ... Isn't working, i don't know why, but i'll gonna try with a new db and macro now with 3 sheet, after lunch. Other thing, Is it possible to choose not the whole line but some just some columns? Thanks again |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract lines with VBA?
The first formula I did it with 3 sheets :)
so the basics it's working The last formula u gave i can control where i put to start in sheet2, this case row B. But i'm not getting to start in line 2 and i can't choose the rows that i will extract. I've changed many variables but i can't either of these cases. Sub comlin3() For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Cells(i, "C").Value = "K" Then - It only works like this, the Range("A1").Value don't work, and i tryed hard iRow = iRow + 1 Cells(i, "C").Resize(, 10).Copy Worksheets("Sheet2").Range("B" & iRow) End If Next i End Sub Can u help again? Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting unused lines between used lines? | Setting up and Configuration of Excel | |||
Deleting Lines in Excel | Excel Discussion (Misc queries) | |||
Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) | |||
Missing lines in chart w/x-axis with months 1-24... | Charts and Charting in Excel | |||
Add more lines and more columns in Excel | Excel Worksheet Functions |