argh, can't get it to work

  • Thread starter Thread starter solo_razor
  • Start date Start date
S

solo_razor

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
 
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
 
-----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
Click to expand...

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
 
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
 
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
 
Back
Top