Selecting every "X" files

[tjak]

I need to audit files produced by others based on a numerical sampling. For instance, I may need to audit every 5th file for one person, but every 12th file for person 2.
Does XL (2007) have an easier way to select every "Xth" file than numbering the lines manually?

 

[obiron]

do you mean lines or files

If it is files then you will need to interrogate the disk directory and use some rules to decide how files are ordered.

If you mean lines then there are number of different ways but the easiest is probably some use of the modulus functionality

Dim sampleRate as Integer
Dim CurrentRow as Long
 
sampleRate = 5
 
For CurrentRow = 1 to ActiveSheet.UsedRange.Rows.Count
  If currentRow mod SampleRate = 0 then
    ' Do something with the row in here...
  End If
next  CurrentRow

 

[tjak]

I'd prefer not using a module. Other suggestions?

 

[obiron]

It doesn't have to be a module, it could be VBA code in the worksheet.

If you mean you would rather not use VBA then the only thing I can think of is to put a formula into every row of the the data set

IV10 =MOD(ROW(A10),5)

and then filter on the result of that cell being 0. The 5 is the nth value that you want to filter by.

 

[tjak]

I believe the formula will do what I need! Thank You!