Row increment formula

[chriscorpion786]

Hi,

I am looking for a formula that will increment numbers by rows, but when i hide some rows in between, the sequence of
numbers should not be disturbed. For e.g 1,2,3,4,5,6 etc.....i tried it with the subtotal formula, but you need a helper column for it. I am looking for a single formula that will increment the numbers while middle rows are hidden.


Thank you in advance.

chriscorpion786

 

[alvin-chung]

Assume column A is your numbering and column B is your non-blank data, try A1=SUBTOTAL(103,B$1:B1) and copy down till last row of your data.
Hide any row and you shall see the numbering remains continuous

 

[chriscorpion786]

Assume column A is your numbering and column B is your non-blank data, try A1=SUBTOTAL(103,B$1:B1) and copy down till last row of your data.
Hide any row and you shall see the numbering remains continuous

Hi and thanks, but i have tried this one. I am looking for a formula without the helper column.
Is there any possibility?

thanks,

 

[Rick Rothstein]

Hi and thanks, but i have tried this one. I am looking for a formula without the helper column.
Is there any possibility?

I do not think you will be able to do that with built-in Excel functions (SUBTOTAL ignores other SUBTOTALs in the range in order to avoid double counting which is an in-column formula cannot be develop with it. However, you can do what you want with a UDF (user defined function)...

Function VisiCount(StartRow As Long) As Long
  Dim Addr As String
  Application.Volatile
  Addr = Cells(StartRow, Application.Caller.Column).Address & ":" & Application.Caller.Offset(-1).Address
  VisiCount = 1 + Evaluate("SUBTOTAL(103," & Addr & ")")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use VisiCount just like it was a built-in Excel function. For your particular request, put a 1 in the first cell you want the count to start in (let's say that is cell C3 for example purposes), then put this formula in cell C4 and copy it down...

=VisiCount(C$3)

Note the absolute row reference (the $ sign in front of the row number you put the 1 in)... that is absolutely necessary in order for the function to be able to count the visible cells correctly.

 

[alvin-chung]

If you have Excel 2010 and above, try take a look at AGGREGATE function - Excel - Office.com

 

[chriscorpion786]

I do not think you will be able to do that with built-in Excel functions (SUBTOTAL ignores other SUBTOTALs in the range in order to avoid double counting which is an in-column formula cannot be develop with it. However, you can do what you want with a UDF (user defined function)...

Function VisiCount(StartRow As Long) As Long
  Dim Addr As String
  Application.Volatile
  Addr = Cells(StartRow, Application.Caller.Column).Address & ":" & Application.Caller.Offset(-1).Address
  VisiCount = 1 + Evaluate("SUBTOTAL(103," & Addr & ")")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use VisiCount just like it was a built-in Excel function. For your particular request, put a 1 in the first cell you want the count to start in (let's say that is cell C3 for example purposes), then put this formula in cell C4 and copy it down...

=VisiCount(C$3)

Note the absolute row reference (the $ sign in front of the row number you put the 1 in)... that is absolutely necessary in order for the function to be able to count the visible cells correctly.

Hi,
Fantastic solution....however....could you please explain this part
Addr = Cells(StartRow, Application.Caller.Column).Address & ":" & Application.Caller.Offset(-1).Address
VisiCount = 1 + Evaluate("SUBTOTAL(103," & Addr & ")")

I am familiar with VBA , but would like to understand what does the "application.caller.column" do and the "evaluate"
Kindly elaborate if you could spare some time....

Very much appreciated.

 

[Rick Rothstein]

Fantastic solution....however....could you please explain this part

Addr = Cells(StartRow, Application.Caller.Column).Address & ":" & Application.Caller.Offset(-1).Address
VisiCount = 1 + Evaluate("SUBTOTAL(103," & Addr & ")")

I am familiar with VBA , but would like to understand what does the "application.caller.column" do and the "evaluate"
Kindly elaborate if you could spare some time....

Let's start with the Evaluate function... it can be used to evaluate text string representations of worksheet formulas. One feature of the Evaluate function is if you give it a range of cells for the embedded worksheet function, it will process the formula as an array formula (sometimes text functions need some extra attention as they are not natively array aware). That is what the Addr variable does inside the Evalute function... it will provide the address of the span of cells we want the SUBTOTAL function to process. That span of cells is the one from the starting cell to the cell above (one less row number) the one the SUBTOTAL function is processing. Okay, now we can look at the first statement you asked about. We know Addr needs to be a worksheet type of address range, so we need to get the address of the starting cell, put a colon after it and follow that by he address of the cell above the cell with the function in it. Application.Caller, when used in a UDF, returns a Range object that references the cell the formula is in. So, if the formulas is in C4, Application.Caller is equivalent to Range("C4"). So, this part of the code line...

Cells(StartRow, Application.Caller.Column).Address

uses the Cells object to from a range reference to the starting cell.... we know the row value (it was passed into the function as StartRow) and we ask Application.Caller to provide the column number via its Column property... then the Address property gives us the address of that cell for use in the Evaluate function. The concatenate colon is obvious. Now the address of the cell above the cell with the function that is being evaluated is the Application.Caller off-setted -1 rows... and, again, the Address property gives us the address of that cell for use in the Evaluate function.

 

[chriscorpion786]

Let's start with the Evaluate function... it can be used to evaluate text string representations of worksheet formulas. One feature of the Evaluate function is if you give it a range of cells for the embedded worksheet function, it will process the formula as an array formula (sometimes text functions need some extra attention as they are not natively array aware). That is what the Addr variable does inside the Evalute function... it will provide the address of the span of cells we want the SUBTOTAL function to process. That span of cells is the one from the starting cell to the cell above (one less row number) the one the SUBTOTAL function is processing. Okay, now we can look at the first statement you asked about. We know Addr needs to be a worksheet type of address range, so we need to get the address of the starting cell, put a colon after it and follow that by he address of the cell above the cell with the function in it. Application.Caller, when used in a UDF, returns a Range object that references the cell the formula is in. So, if the formulas is in C4, Application.Caller is equivalent to Range("C4"). So, this part of the code line...

Cells(StartRow, Application.Caller.Column).Address

uses the Cells object to from a range reference to the starting cell.... we know the row value (it was passed into the function as StartRow) and we ask Application.Caller to provide the column number via its Column property... then the Address property gives us the address of that cell for use in the Evaluate function. The concatenate colon is obvious. Now the address of the cell above the cell with the function that is being evaluated is the Application.Caller off-setted -1 rows... and, again, the Address property gives us the address of that cell for use in the Evaluate function.

Thank you very much...
How does one polish himself in VBA, i really want to master it. Will it help if i learn VB.net , would it help for my VBA knowledge and learning about the object model. Excel 2013 VBA help has been eliminated, what a shame, you have to connect to the internet always.
Thanks anyways for the explanation.....