4.8 FAQ-367 How do I delete every nth row or column from my worksheet?

Del-Every-nth-Row-or-Col

Last Update: 8/9/2018

Delete every nth row or column

by Reduce Columns/Rows dialog

To delete columns, select Worksheet: Reduce Columns. This will open the wreducecols X-Function.

For example, set the dialog as screenshot below will delete every 5th column in the active workbook (delete 1 column and skip 4 columns, start from the 5th column).

Or, you can run script below in the Command Window directly to execute the abvove operation:

wreducecols skip:=4 start:=5;

To delete rows, select Worksheet: Reduce Rows. This will open the wreducerows X-Function.

For example, set the dialog as screenshot below will delete every 4th row in the active workbook (delete 1 row and skip 3 rows, start from the 4th row)

Or, you can run script below in the Command Window directly to execute the above operation:

wreducerows nrows:=1 skip:=3 start:=4;

by looping

With your worksheet active, open the Script Window (menu item: Window: Script Window), copy-paste the following lines of code, select all lines, and press Enter.

To delete columns:

// Delete every nth column, counting from the left
int ndel = 3; // change this number as needed;

int ncols = wks.ncols;
int nlast = ncols - mod(ncols, ndel);

// Start deleting from the right to the left
for(int ii = nlast; ii > 0; ii -= ndel)
{
   delete wcol(ii);
}

To delete rows:

// Delete every nth row, couting from the top
int ndel = 3; // change this number as needed;

int nrows = wks.nrows;
int nlast = nrows - mod(nrows, ndel);

// Start deleting from the bottom to the top
for(int ii = nlast; ii > 0; ii -= ndel)
{
  wks.DeleteRows(ii);
}

Delete every nth columns using Select dialog

1. With Worksheet active, select Edit: Select from main menu.
2. In the Select dialog that opens, select By Select N columns and Skip M columns from Mode drop-down list, and then specify N, M and start column index.
3. Click Test-Select if True button to select the desired columns. Click Hide to escape the dialog.
4. In the worksheet, right click on any selected column and choose Delete from the context menu.

For example, set the dialog as screenshot below will select every 5th column in the active worksheet (select 1 column and skip 4 columns, start from the 5th column).

Click Test-Select if True button and then Hide button. Return to the worksheet, delete these columns by selecting Delete from right-click menu. This will delete every 5th column.

Delete every nth row using the Worksheet Query dialog

1. With the worksheet active, open the Worksheet Query dialog by clicking Worksheet: Worksheet Query from main menu.
2. Enter mod(i,3)==0 (refer to mod function) in the Condition box, which means select the every 3rd row. Then click the Test -- select if True button to highlight these rows.

   

3. Close this dialog and delete these rows from worksheet by selecting Delete from right-click menu.

Keywords:remove, reduce

Minimum Origin Version Required: 2016 SR0