selecting every eg 5th cell in a worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do i select every nth cell in worksheet. For example, if i wanted to
take a value at the beginning of each week (every 7th cell). Can i do this in
excel?

Stephen.
 
You'll need a "helper" cell for this.

Assumption: data is in Cells: B2:B100 and is dates
A2: =WEEKDAY(B2,2)=1
Copy the formula down as far as you need it.

That formula will return TRUE for each column B value that is a Monday.
You could then use Data>Filter>Autofilter to select only TRUE values

OR

Assumption: data is in Cells: B2:B100 and is not Excel dates
A2: =MOD(ROW()+5,7)
copy that formula down

Adjust the 5 in the formula until the return value of 0 (zero) flags the
Mondays.
This time use Data>Filter>Autofilter to select only zero values.

Alternatively, using either formula you could select Col A,
then Edit>Find
Find What: 0 or TRUE (depending on which formula you used)
Look In: Values
Click [Find All]
Then hold down the [Ctrl] key and press A
That will actually select all of the zero or TRUE cells, respectively.

Does either method help?

***********
Regards,
Ron
 
something like this in a macro

Sub everynthcell()
For i = 1 To 31 Step 7
MsgBox Cells(i, "f")
Next
End Sub
 
Back
Top