Autofill numeration in row until last empty cell

[OlbudMaciej]

Hi,
How can I create macro which can autofill numeration in row.
It have to end this macro at the end of this table.
I was trying to make it in this way:

It worked perfect, but in empty sheet.
In my main sheet I got an error 1004 because of merged cells size.

Sub formuly_cena_zakupu_dziala()
'
' formuly_cena_zakupu Makro
'

'
Range("J18").AUTOFILL Range("J18:J" & Cells(Rows.Count, "J").End(xlUp).Row), Type:=xlFillSeries




End Sub

[#2]

@OlbudMaciej 

with out VBA:

=IF($B2<>"",COUNTA($B2:$B$2),"")

or with VBA

Sub Nummero()
Dim x As Long, j As Long, Z As Long
x = Cells(Rows.Count, 2).End(xlUp).Row
For j = 1 To x
    If Not IsEmpty(Cells(j, 2)) Then Cells(j, 1) = Z + 1: Z = Z + 1
Next
End Sub

Example file is included.

Hope I could help you with these information.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@NikolinoDE 

Thank you for help. 
I copy and paste your code but it's not working. 
I don't get ant error when I starting your code but it will do nothing. 

[#4]

In your table it works but I don't know how to change column what the number will write.
In your example number are in A column and checking values in B column.
In my case numbers should be write in J column and check if empty value i K column.

[#5]

Which Excel version do you have?...operating system, storage medium?
Does the inserted file work for you?

[#6]

https://drive.google.com/file/d/1kHRWKOA6HPGdMwsHp9Hb1QBISxLJw6ER/view?usp=sharing

Please check in my example

[#7]

@OlbudMaciej 

Attached is the example in the file.

If I may recommend that you use the formula in your projects, I think it will be of more use to you.

Example with formula you will find in column "J".

When you write something in column K, the numbering appears as desired.

[#8]

Its great idea and off course it working.
But I sometimes adding more rows belowe table. When I adding row the formula is not copying.
Thats why I would like to use VBA.
Thank you

[#9]

@OlbudMaciej 

I would like to update numeration to the end  until something is next to the numeration column. 

[#10]

When inserting a row, you just need to drag down the formula. Click the cell and right until cross appears and just pull down.

With VBA code, in your case, a line of code would also have to be inserted for each table and if the lines change, then the code would have to change accordingly. I think this would be even more cumbersome for you.

[#11]

@OlbudMaciej 

You can pull this off without VBA.  Please consider this solution:

'Dynamic range for the entries in Column B
=LET(b,Sheet1!$B$2:$B$100000,nonblank,COUNTA(b),TAKE(b,nonblank))

Sheet level formula:

The results of SEQUENCE are only as long as the number of rows from the dynamic range.
=LET(r,ROWS(Dynamic),SEQUENCE(r,,1,1))