Return the row number

[ratbags21]

I have extracted data that looks like this:
Col1 Col2 Col3 Col4
Row 1 Start
Row 2 1178 1000 53.75 60.00
Row 3 2200 1000 42.67 43.00
Row 4 3450 1000 76.89 75.00
Row 5 Start
Row 6 1178 1100 34.44 34.00
Row 7 2200 1100 89.29 85.00

Now, Col1 is a nominal account number, Col2 is a department
Col3 is actuals and col4 is budget.
The "Start" is there to help the VBA delineate departments
What i need to do is select, in this instance, row2/Col1 to
Row4/Col4 and paste that into a seperate worksheet.
Any (simple) ideas on how I might do this. An xldown in
col1 is not possible (but it is in col2)!!

Any help much appreciated

 

[Andrew Poulsom]

Something like this?

Sub Test()
    With Worksheets("Sheet1")
        .Range("A2:A" & Range("B2").End(xlDown).Row).Resize(, 4).Copy Worksheets("Sheet2").Range("A1")
    End With
End Sub

 

[ratbags21]

somthing like this?

Thanks Andrew.
However, i need to substitute most of the formula with variables
in other words, I do not know how many rows each dept has. Now, I can get the row number by doing an "xldown" in col2 and saving that (activecell.row) to a variable but in your "range" syntax how do I substitute an "A4" type ref with the row?

 

[texasalynn]

you could assign variables to the start range like this
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FindRow()
    <SPAN style="color:#00007F">If</SPAN> Range("a1").Text = "Start" <SPAN style="color:#00007F">Then</SPAN>
    FirstFind = ActiveCell.Offset(1, 0).Address
    <SPAN style="color:#00007F">Else</SPAN>
    FirstFind = Range("a:a").FindNext(After:=ActiveCell).Offset(1, 0).Address
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    FirstNext = Range("a:a").FindNext(After:=ActiveCell).Offset(1, 0).Address

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>