|
Dynamic Range Wizard Step 3
In Step 3 you choose the expansion method to use for the rows of your dynamic range, or confirm the anchor selection and fixed rows if you are expanding only by columns.
Choosing the expansion method for the last row.
.png)
Last non-empty row.
|
Because this method finds the last visible cell it should be used with care on ranges that are filtered, contain hidden rows, or contain rows with zero height.
|
This method looks up from the last row to find the last visible non-empty cell in a column. This is the default re-sizing method.
You can use this method even if your data contains blanks. The last cell in a column is the last visible cell that contains any character (including ‘), number or formula. Empty cells that have been formatted are ignored.
See COUNTROWS2 for more details.
|
Last contiguous (non-empty) row.
|
This method is not recommended for ranges that are filtered, contain hidden rows, or contain rows with zero height
|
This method looks down from the anchor row to find the first visible empty cell, and re-sizes to include all the rows from the anchor row to the row before the empty cell. This results in a range of all the contiguous (adjacent) non-empty cells in the column.
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 vertically on the same worksheet.
See COUNTCONTIGROWS2 for more details.
|
Last row in the used range.
This method looks down from the anchor row to the last row 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, not just the columns you have selected for the dynamic range.
See COUNTUSEDROWS2 for more details.
Count of non-empty cells in the anchor column.
This method uses Excel’s COUNTA function to count non-empty cells from the anchor cell down to the last row.
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 rows.
Choosing this option will show a reference box for you to select the cell that contains a formula giving the number of rows in the dynamic range.
Use this option when:
You have a large number of dynamic ranges that all have the same number of rows, so that it is more efficient to only find the number of rows once.
You are using a special calculation to determine the number of rows.
Step3A: Choose columns for finding the last row
These two additional options are only available if you have selected either “Last non-empty row” or “Last contiguous non-empty row”, and you have chosen to expand only by rows.
Use only the Anchor Column to find the last row.
This option finds the last row only in the Anchor Column of your dynamic range. Use this option if all the columns in your dynamic range will always have the same last row, or if you want the last row to always be determined by the Anchor column.
Use all the dynamic range columns to find the last row.
This option looks in all the columns of your dynamic range and uses the largest last row found. Use this option if some of the cells in the last row of your dynamic range may be empty.
The calculation time for this option increases with the number of columns in your dynamic range.
Confirm the Anchor Cell and fixed columns
If you selected fixed rows and dynamic columns in Step 2 you are asked to confirm your selection in Step 3.
.png)
If you are happy with your selection press Next to go to Step 4.
|
