I want to Remove every 5 rows from my excel sheet. Is there a way to automate it?

E.g. I want to remove row 2 to 6, 8 to 12 and so on until the end of file.
Please advice.
Row 1,7,13 etc will retain.

In Sheet2

in A!

=INDEX(Initial!A$1:A$18,(ROWS($1:1)-1)*6+1)

Copy across and down

Sorry bro but can you explain the commands?
Because it is an entire excel sheet and this is just sample.

For large range use

=INDEX(Initial!A:A,(ROWS($1:1)-1)*6+1)

INDEX function is INDEX(range, row #, column #)


=INDEX(Initial!A$1:A$18,(ROWS($1:1)-1)*6+1)


(ROWS($1:1)-1)*6+1 calculates the row number

ROWS($1:1) is "counter" which in increase by 1 as you drag the formula down the rows

rows($1:1) =1, rows($1:2) =2 which when applied to the above selects row 1, 7,13 etc

Hi,
I analyzed the formula and I got 3 questions.

Q1 What is the difference between putting INDEX(A$1:A$18, row_num) and INDEX(Initial!A$18:A$18, row_num)?
What does the initial! do ?

Q2 Can I put INDEX(A$1:B$18, row_num) ? In the words, it is array from A to B, If so how should I edit the formula?

Q3 Is there a way to drag excel formula down automatically instead of using a mouse?
Thanks.

Q1. "Initial" is the Sheet name! where you want to extract the data from

Q2. It would not achieve anything: put formula in A2 drag to B (so formula in B will be =INDEX(Initial!B:B,(ROWS($1:1)-1)*6+1)

Select both columns and drag down.

Q3: if Q2. is too difficult, use the VBA solution ; this deletes from "Initial" so once it has run you have lost the original data.

Thank you for replying.
What is the syntax for the sheet name may I ask?

Sheet (Tab) name followed by "!" so

Sheet1! A1

Sheet2!:A2:B4

If name includes blanks or special characters e,g "My Sheet"

then tab name is in single quotes ..

'My sheet'!A2:Z50

Thanks this forum is very informative.
Can I NOT put the sheet name instead?
If not, how does the formula know which sheet to select?

All explained in post #9. In a formula, it is the name given to a sheet. Excel by default names sheets as Sheet1, Sheet2 etc. If you rename them , then use this name.

There are numerous on-line Excel courses which will help you get started.

I understand. But if I dont put the sheet name, it will just refer to the current excel sheet I am editing right?

Correct! It will assume the sheet in which the formula is placed.