Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
argh, can't get it to work
Hello,
I have a problem with a excel sheet. The sheet contains 250 rows of data. I want to extract rows that contain a specific value in a column. e.g. a b c d 1 / 2 3 THIS ONE 3 * 3 2 1 / 3 6 THIS ONE 3 / 9 9 THIS ONE 3 * 7 7 3 / 3 2 THIS ONE Now the rows with a / value in column b have to be extracted and copied into another sheet. I hope somebody can help me. Regards, Niek ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
argh, can't get it to work
Sub Macro1()
Dim iRow1 As Long Dim iRow2 As Long Dim ws1 as Worksheet Dim ws2 as Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") For iRow1 = 1 to 250 If ws.Cells(iRow1, 2) < "/" then iRow2 = iRow2 +1 ws1.Range("A" & iRow1 & ":D" & iRow1).Copy _ Destination:=ws2.Range("A" & iRow2 & ":D" & iRow2) End If Next iRow1 End Sub HTH, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
argh, can't get it to work
-----Original Message----- Hello, I have a problem with a excel sheet. The sheet contains 250 rows of data. I want to extract rows that contain a specific value in a column. e.g. a b c d 1 / 2 3 THIS ONE 3 * 3 2 1 / 3 6 THIS ONE 3 / 9 9 THIS ONE 3 * 7 7 3 / 3 2 THIS ONE Now the rows with a / value in column b have to be extracted and copied into another sheet. I hope somebody can help me. Regards, Niek ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ This code should work as long as the sheet with the data in it is the active sheet. Substitute "sheet2" on line 8 with the target sheet name. Columns("B:B").Select Selection.AutoFilter Range("A1").Select Selection.AutoFilter Field:=1, Criteria1:="/" Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste Range("A1").Select |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
argh, can't get it to work
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, something like the following will work: arr = ArrayRowFilter1(Sheets(4).Range("A1:D6"), 2, "/") Sheets(3).Range("A1:D" & UBound(arr)).Value = arr Alan Beban solo_razor wrote: Hello, I have a problem with a excel sheet. The sheet contains 250 rows of data. I want to extract rows that contain a specific value in a column. e.g. a b c d 1 / 2 3 THIS ONE 3 * 3 2 1 / 3 6 THIS ONE 3 / 9 9 THIS ONE 3 * 7 7 3 / 3 2 THIS ONE Now the rows with a / value in column b have to be extracted and copied into another sheet. I hope somebody can help me. Regards, Niek ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
argh, can't get it to work
You could also try just doing a filter and copy paste like this one
Selection.AutoFilter Field:=2, Criteria1:="=*/*", Operator:=xlAnd Columns("A:A").Copy Sheets("Medarbejderdata").Select Cells.Select ActiveSheet.Paste ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
SUMPRODUCT problems...argh... | Excel Discussion (Misc queries) | |||
formula I can't figure it out?! ARGH | Excel Worksheet Functions | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel |