Cut to EOF
 

I have a workbook with 2 worksheets
One worksheet is empty, the other is used - 26 columns by 1,000 +/- rows
Column' B' in the worksheet with data has either a "1" or "2" in it.
The worksheet is sorted by column 'B' assending so all the '2's are on the
bottom
I search column 'B' to FIND the first '2'. When I find it I am in Column 'B'.
This is where I need help. Using Excel VB programing
I want to cut this and all following (entire) rows with data (from column
'A' to 'Z')
and go to the empty worksheet and paste all cut rows.
I am using Excel 2002.
Thank You In Advance
Bill

Cut to EOF
 

Try this

sub move_2s_to_sheet2()

range([b:b].find(what:="2"), cells(rows.count,
2).end(xlup)).entirerow.cut _
sheets("sheet2").[a1]

end sub



Col


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=547451

Cut to EOF
 

Bill

Autofilter is your best option here (it's quicker) - something along
the lines of:

Sub Test()
Dim rng as Range
'amend references to worksheets/ranges to suit
Set rng = Sheets("Sheet2").Range("A1:Z" &
Sheets("Sheet2").Cells(65536,2).End(xlUp).Row)
With rng
.Autofilter Field:=2, Criteria1:="2"
.SpecialCells(xlCellTypeVisible).Copy
Destination:=Sheets("Sheet1").Range("A1")
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Autofilter
End With
End Sub


Hope this helps!

Richard

BillScuorzo wrote:

I have a workbook with 2 worksheets
One worksheet is empty, the other is used - 26 columns by 1,000 +/- rows
Column' B' in the worksheet with data has either a "1" or "2" in it.
The worksheet is sorted by column 'B' assending so all the '2's are on the
bottom
I search column 'B' to FIND the first '2'. When I find it I am in Column 'B'.
This is where I need help. Using Excel VB programing
I want to cut this and all following (entire) rows with data (from column
'A' to 'Z')
and go to the empty worksheet and paste all cut rows.
I am using Excel 2002.
Thank You In Advance
Bill