Dynamic Range Wizard Step 4

Navigation: FXLV4_Manager > Dynamic Range Wizard >

In Step 4 you choose the expansion method to use for the columns of your dynamic range, or confirm the anchor selection and fixed columns if you are expanding only by row.

Choosing the expansion method for the last column.

Last non-empty column.

Because this method finds the last visible cell it should be used with care on ranges that contain hidden columns, or contain columns with zero width.

This method looks left from the last column to find the last visible non-empty cell in a row. This is the default re-sizing method.

You can use this method even if your data contains blanks. The last cell in a row is the last cell that contains any character (including ‘), number or formula. Empty cells that have been formatted are ignored.
See COUNTCOLS2 for more details.

Last contiguous (non-empty) column.

This method is not recommended for ranges containing hidden columns or columns with zero width.

This method looks to the right from the anchor column to find the first visible empty cell, and re-sizes to include all the columns from the anchor column to the column before the empty cell. This results in a range of all the contiguous (adjacent) non-empty cells in the row.

Use this method if you want your dynamic range to be delimited by a blank/empty cell. This is useful when you have multiple tables stacked adjacent to one another on the same worksheet.

Do not use this method on ranges containing hidden columns or columns with zero width.
See COUNTCONTIGCOLS2 for more details.

Last column in the used range.

This method looks to the right from the anchor column to the last column in Excel’s used range.

This will include all cells on the worksheet with any formatting even if they are empty, and will include all the columns and rows, not just the rows you have selected for the dynamic range.
See COUNTUSEDCOLS2 for more details.

Count of non-empty cells in the anchor row.

This method uses Excel’s COUNTA function to count non-empty cells from the anchor cell across to the last column.

If your data includes any empty cells this method will not include all the cells including data.

Use this method only if you require compatibility with users who do not have FastExcel installed.

Select the cell that will contain the number of columns.

Choosing this option will show a reference box for you to select the cell that contains a formula giving the number of columns in the dynamic range.

Use this option when:

You have a large number of dynamic ranges that all have the same number of columns, so that it is more efficient to only find the number of columns once.

You are using a special calculation to determine the number of columns.

Step4A: Choose rows for finding the last column

These two additional options are only available if you have selected either “Last non-empty column” or “Last contiguous non-empty column”, and you have chosen to expand only by columns.

Use only the Anchor Row to find the last column.

This option finds the last column only in the Anchor Row of your dynamic range. Use this option if all the rows in your dynamic range will always have the same last column, or if you want the last column to always be determined by the Anchor row.

Use all the dynamic range rows to find the last column.

This option looks in all the rows of your dynamic range and uses the largest last column found. Use this option if some of the cells in the last column of your dynamic range may be empty.

The calculation time for this option increases with the number of rows in your dynamic range.

Confirm the Anchor Cell and fixed rows

If you selected fixed columns and dynamic rows in Step 2 you are asked to confirm your selection in Step 4.

If you are happy with your selection press Next to go to Step 5.