How to number rows using macros

[Arafat]

Hello all,

I need to renumber the columns in ColumnA after every null value,initially ColumnA is null and columnB is populated
this how my data should be formatted :-

columnA columnB
1 text
2 text
3 text
Null Null
1 text
2 text
3 text
Null Null​

Your Help will be greatly appreciated Peace

 

[MickG]

Try :-

[COLOR="Navy"]Sub[/COLOR] MG03May16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = c + 1
    [COLOR="Navy"]If[/COLOR] Dn <> "" [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, -1) = c
    [COLOR="Navy"]Else[/COLOR]
        c = 0
        Dn.Offset(, -1) = ""
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> NumberRows()<br>    Rows(1).Insert<br>    <SPAN style="color:#00007F">With</SPAN> Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)<br>        .Formula = "=IF(B2="""","""",IF(B1="""",1,A1+1))"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Rows(1).Delete<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[JamesW]

Hi,

Welcome to the board.

By Null do you mean that there is the text string "Null" in the cell?

This might work for you:

    Dim lRow, x As Integer
    
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lRow
        If Cells(i, 1).Value = "Null" Then
            x = 0
        Else
            x = x + 1
            Cells(i, 1).Value = x
        End If
    Next i

 

[ogo]

Please check if the below code woks as expected

Sub ReNumber()
x = Cells(Rows.Count, 2).End(xlUp).Row
Count = 1
For i = 1 To x
'Assuming that by null you meant empty cell    
If Not Cells(i, 2).Value = "" Then
        Cells(i, 1).Value = Count
        Count = Count + 1
    Else: Count = 1
    End If
Next
End Sub

 

[mirabeau]

Hi,

Looks like you have plenty of possibilities.

Maybe you might like to also try this one.

Sub restartcount()
Dim e As Range
Set e = Range("B1")
If e = "" Then e = e.End(4)
Do While e.Row < Rows.Count
e.Offset(, -1) = 1
If e(2) = "" Then
    Set e = e.End(4)
Else
    Range(e, e.End(4)).Offset(, -1).DataSeries
    Set e = e.End(4).End(4)
End If
Loop
End Sub

 

[Arafat]

Thank you Thank you.... Wow U ppl are legends every solution works
never thought i will get solution so soon.... great Minds....