< Go Back

Last row in mixed data with no blanks

If you want to determine the last relative position for a mixed data that has no empty cells then the Excel built-in function called the COUNTA can be used to perform this operation.

Generic Formula

=COUNTA(rng)

Note on last *relative * position

  1. When you need to create more advanced formulas, then it is always to find the last location of the data in the specified list.
  2. Depending on data, the last location can be the last column containing data or last row containing data or intersection of them.
  3. Also, note that we are always after the last “relative position “ in a given range and not in the row number on the spreadsheet.

How does the Generic formula work?

  1. The COUNTA built-in function of Excel is usually used to count values in a range.  Therefore this function can count both numbers and texts so it goes well with the mixed data.
  2. Note that this approach will not pass if the range consists of blank cells.

Note

To construct a dynamic range we can use this formula with OFFSET and INDEX function.

Example

Let us consider a list of games (texts)  and numeric values such as dates, numbers etc. The Generic formula with the range parameter contains the range of cells from  A2 to A50. Let the last value entered be at A8. Therefore, when used this range inside a COUNTA function, generates the last relative Position I.e,7, as shown In the diagram below.

Figure 1. The COUNTA function used to return the relative position of the last row

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar