I want to get the row number of the first visible row on one of my sheets. So for example if rows 1 - 499 were hidden then I want to write a formula that returns the numerical value 500.

I know this will probably require the usage of the ROW function but I'm not sure how to write the required condition.

1 Answer 1

  1. Let's assume your data is in column A
  2. Create a new column B and write =row() into every cell of that column, so it will contain the row number
  3. Use the following formula to calculate what you need:


It works like these: subtotal(105,range) shows you the minimum value of the range that is not hidden.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.