Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding the number of rows and the first row with a specific value
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 | +-------------------+---------------------------------------------------+ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding the number of rows and the first row with a specific value
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 "Bruce Bowler" a écrit dans le message de ... 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 | +-------------------+---------------------------------------------------+ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding the number of rows and the first row with a specific value
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) "Bruce Bowler" wrote in message ... 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 | +-------------------+---------------------------------------------------+ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding the number of rows and the first row with a specific value
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") -- Regards, Tom Ogilvy "Bruce Bowler" wrote in message ... 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 | +-------------------+---------------------------------------------------+ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding the number of rows and the first row with a specific value
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 | +-------------------+---------------------------------------------------+ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding a number within a text and numbers for multiple rows | Excel Worksheet Functions | |||
Finding count of unique number within a specific criteria? | Excel Worksheet Functions | |||
Finding a number of values that adds up to a specific value | Excel Discussion (Misc queries) | |||
Finding Number of cells in a column of other sheet having a specific word in them | New Users to Excel | |||
Finding cells with a specific number of characters | Excel Discussion (Misc queries) |