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/

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

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

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/

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/