Return the row number

ratbags21

New Member
Joined
Jul 13, 2006
Messages
17
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Something like this?

Code:
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

New Member
Joined
Jul 13, 2006
Messages
17
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,113,952
Messages
5,545,143
Members
410,666
Latest member
Al3cs
Top