Nth Select

Hi Manu

Do you mean via VBA ? if so use:

Range("A1:E100").Select

Dave

OzGrid Business Applications

Thanx for your answer Dave but I am not sure what you really mean. What is a VBA??

I am a rookie at using EXCEL, maybe I should have clarified that...

Would you be so kind to explain the steps I need to take to accomplish the task.

Thanx a lot for your time

Regards

Manu

Sorry Manu, forget the VBA (Visual Basic for Applications) bit.

There are many ways you can select a range, you can either left click on the top left cell of the range you want and drag.
You can type the address of the range you want to select in the "Name box" ,this is to the left of the formula bar and displays the address of the Active cell. So if you wanted to select the range A1 to D20 ypou would type: A1:D20
You can also push F5 and type the address in the Refrence box.

Dave

OzGrid Business Applications

Got that Dave

What I am looking for is the ability to select every 5th or 10th(for e.g.) record out of say 5000 records that I have.

Could you guide mo doing that in Excel.

Thanx

Manu

We will need to use a bit of trickery for this.

Let's say Column D is blank. Try this.

1. Put the number 1 in cell D6 to D9
2. Select cells D5:D10
3. Left click on the Fill handle (small black square bottom right of D10)
4. Holding down the Mouse button drag down to the last row of you data. Then release.
5. Push F5 and click Special. Then tick "Constants"
6. Click Ok. Now go to Format>Rows>Hide.
7. Select all cells within your data range and again push F5 and click Special.
8. Tick "Visible Cells" and click Ok.

You should now only have every fifth row of your data selected.

Dave
OzGrid Business Applications

How about using the INDIRECT function. Using the following assumptions:
• You want to input the number for "Nth" in cell A1.
• Your data table is in a sheet called "Sheet2".
• Your selected will begin in row 2 (cell A2).

Input the following formula in cell A2;
=INDIRECT("'Sheet2'!"&"R"&(ROW()-1)*$A$1&"C"&COLUMN(),0)

Then copy that formula across columns and down rows.

Hope this helps. If not, or if I'm not clear, let me know.

Barrie

I attempted what you suggested but keep getting #REF! error. Please advise further steps.

Thanx Manu

I tried your method but did not work. Just to re-state my problem. I need to identify & extract every 5th record to another sheet from 5000 records available to me.

Your help & time is much appreciated.

Manu

Hi again Manu. Could you post the formula that is giving you #REF!

Barrie

Hello Barrie,

Here's the formula that I am entering in cell A2 of Sheet1. Let me also state my understanding based on your directions.

My Data is within the same workbook on Sheet2
I am entering =INDIRECT("'Sheet2'!"&"R"&(ROW()-1)*$A$1&"C"&COLUMN(),0) to cell A2 of Sheet1

I am unable to copy this formula down the rows however it does allow me to copy across columns.

Your time and patience is much appreciated.

Thanks

Manu

What do you have in cell A1, you should have the number 5 if you are trying to retrieve every fifth record.
Also, you should have no problem copying this formula down rows. What happens if you try to copy this down to cell A3?

Well Well

Thank You for all the help Barrie

I think it is working

I profusely thank you for your help

Regards

Manu