finding the number of rows and the first row with a specific value

[Bruce Bowler]

I have a bunch of spreadsheets where I want to

sort the entire sheet on column A and then column B (that part I can
handle)

The I want to find the first row in column A that has a 1 in it and move
everything from that row thru the last row into a new sheet in the same
workbook.

I know how to do it all manually, but since there are a bunch of these
files I'd like to do it in VBA. The part I'm not quite sure how to do
efficiently (I know how to do it inefficiently) is find the row number of
the last row and the row number of the first row that has a 1 in column A.

Any help and/or pointers would be appreciated...

Excel 2003 if it matters.

Thanks!
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | A fox should not be of the jury at a goose's trial.
1.207.633.9600 | - Thomas Fuller
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[papou]

Hi

find the row number of the last row

MsgBox "The last row in active sheet is row No " _
& ActiveSheet.Range("A65536").End(xlUp).Row

row number of the first row that has a 1 in column A

MsgBox ActiveSheet.Range("A:A").Find(1, , xlValues, xlWhole, xlByRows).Row

You may change Activesheet with Worksheets("YourSheetNameHere")
HTH
Cordially
Pascal

 

[Bob Phillips]

Last row

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

First 1

On Error Resume Next
Set oCell = Columns(1).Find 1
On Error Goto 0
If Not oCell Is Nothing Then
...

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Dim rng as Range, rng2 as Range
set rng = columns(1).find(1,Range("A1"))
set rng2 = cells(rows.count,1).End(xlup)
rng.Resize(rng2.row - rng.row + 1,256).copy _
Destination:=worksheets(2).Range("A1")

 

[Bruce Bowler]

Thanks to papou, Bob and Tom. Works like a champ...

--
+-------------------+---------------------------------------------------+
Bruce Bowler | The meek may inherit the earth, but it's the grumpy
1.207.633.9600 | that get promoted - Father Francis Mulcahy
(e-mail address removed) |
+-------------------+---------------------------------------------------+