Select Variable Ranges - VBA

[BatFace]

Thank You in advance to anyone who can help with this one.

Excel 2000 - vba

I have a spreadsheet which has imported data, updated monthly - the number of rows are variable. Column A is pre-sorted into id numbers and I am after code which will select all rows which start with a certain id number - then I can write the code to copy and paste the selected data into another sheet (I can do that part!). But if possible I wanted to have the rows highlighted in a block - there are up to 800 rows per id number.

I have done various searches in FAQ etc, but have been unable to find what I need.

Thanks again!

 

[GeekGirlau]

Have you tried using the advanced filter function? You need 3 ranges: your data list, your criteria and a location to copy them to.

Your criteria range should contain the name of the field, and immediately below that the id number you want to extract. Then select Data, Filter, Advanced Filter. Select Copy to Another Location, and enter the list range (your data), the criteria range, and the cell you want to copy the data to.

If this is a regular function you may want to look at creating this in code - you can record most of it (it helps if you name the ranges).

 

[xlbo]

Hi - use this to filter - for data in A:Z :

Sub copyIDData

dim myID as string, lRow as integer
lRow = range("a65536&quot.end(xlup).row

myID = inputbox("Enter ID to copy data for&quot

Columns("A:Z&quot.Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:= myID
range(cells(2,1),cells(lRow,26)).interior.colorindex = 3
-----previous line will highlight the selection in the original sheet
range(cells(1,1),cells(lRow,26)).copy (Sheets("TargetSheet&quot.range("StartRange&quot)
-----and this line will copy only the selected data
application.cutcopymode = false
Sheets("OriginalSheet&quot.AutoFilterMode = False
end sub

HTH
Geoff

 

[BatFace]

Sorry I haven't gotten back - I have been away for a few days.

Both solutions worked brilliantly - thanks so much for your help!