Serial Number on filled rows

[Chandresh]

HI Team,
Need help creating a macro in plotting serial numbers in excel in column A which has some blank rows. Cell A9 is the first row from which the numbering starts so my cell A9 value is “1” After that there are some blank rows that are not fixed and some rows have numbers. I need a macro that can plot numbers in the next filled row by replacing the existing value. For example, if cell number A15 has a value of 3, then the macro should return the value as 2 and follow the same series 3,4, 5till the last filled rows.

Thanks in advance.

 

[RudRud]

Is it something like this?

Book1
	A	B
2	1	1
3
4	3	2
5
6
7	5	3
8
9	6	4
Sheet2

Sub test()

For Each ss In Range("a2:A" & Cells(Rows.Count, "a").End(xlUp).Row)
    If CInt(ss.Value) >= 1 Then
        k = k + 1
        ss.Offset(0, 1).Value = k
    End If
Next ss


End Sub

 

[mohadin]

Hi
How about

Sub test()
Dim a
Dim i&,k&
    a = Cells(9, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 8, 2)
    k = 1
    For i = 1 To UBound(a)
        If Not IsEmpty(a(i, 1)) Then a(i, 2) = k: k = k + 1
    Next
    Cells(9, 2).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), 2)
End Sub

 

[Chandresh]

Hi
How about

Sub test()
Dim a
Dim i&,k&
    a = Cells(9, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 8, 2)
    k = 1
    For i = 1 To UBound(a)
        If Not IsEmpty(a(i, 1)) Then a(i, 2) = k: k = k + 1
    Next
    Cells(9, 2).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), 2)
End Sub

Macro is working fine, however, I need the output in column A but the macro is giving output in column B, could you please help to get the output in column A

 

[Chandresh]

Is it something like this?

Book1
	A	B
2	1	1
3
4	3	2
5
6
7	5	3
8
9	6	4
Sheet2

Sub test()

For Each ss In Range("a2:A" & Cells(Rows.Count, "a").End(xlUp).Row)
    If CInt(ss.Value) >= 1 Then
        k = k + 1
        ss.Offset(0, 1).Value = k
    End If
Next ss


End Sub

yes the output should be as per the snip you attached however the output should come in column A not column B

 

[RudRud]

Sub test()

For Each ss In Range("a2:A" & Cells(Rows.Count, "a").End(xlUp).Row)
If CInt(ss.Value) >= 1 Then
k = k + 1
ss.Value = k 'Delete offset so output will be in Column A
End If
Next ss


End Sub

Is it what you're looking into? how about current values? put in column b?

 

[Peter_SSs]

A non-looping option to try. I have assumed that A8 does not contain a number.

Sub ReNumber()
  With Range("A9", Range("A" & Rows.Count).End(xlUp))
    .SpecialCells(xlConstants, xlNumbers).FormulaR1C1 = "=IFERROR(LOOKUP(9^9,R8C:R[-1]C),0)+1"
    .Value = .Value
  End With
End Sub