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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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>
 

Forum statistics

Threads
1,144,376
Messages
5,724,001
Members
422,530
Latest member
Badpoisondwarf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top