Auto Row Numbering using VBA

[leviackerman13]

Hi. I have a large data set where i need to add a word "Count" and then number the rows. The data set looks like the image below. What i want to do is in the red font. Hoping for your help as i am new to VBA.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this would do.

Sub Row_Numbering()
  With Range("B2:B" & Range("C" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Name"",""Count"",N(R[-1]C)+1))"
    .Value = .Value
  End With
End Sub

 

[leviackerman13]

Welcome to the MrExcel board!

See if this would do.

Sub Row_Numbering()
  With Range("B2:B" & Range("C" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Name"",""Count"",N(R[-1]C)+1))"
    .Value = .Value
  End With
End Sub

Thanks a lot. This has worked for me.

 

[leviackerman13]

Welcome to the MrExcel board!

See if this would do.

Sub Row_Numbering()
  With Range("B2:B" & Range("C" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Name"",""Count"",N(R[-1]C)+1))"
    .Value = .Value
  End With
End Sub

Hi. I would like to ask for your help again. I am trying to auto sum the grades of every student and output the sum in the last row. What i want to do is in the red font.

 

[Peter_SSs]

Looks like you have changed columns. So this should do both jobs if student names are in column B.

Sub Row_Numbers_and_Totals()
  Dim rA As Range
  
  With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Name"",""Count"",N(R[-1]C)+1))"
    .Value = .Value
    For Each rA In Range(.Address).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Offset(rA.Rows.Count, 2).Resize(1, 2).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
    Next rA
  End With
End Sub

 

[leviackerman13]

Looks like you have changed columns. So this should do both jobs if student names are in column B.

Sub Row_Numbers_and_Totals()
  Dim rA As Range
 
  With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Name"",""Count"",N(R[-1]C)+1))"
    .Value = .Value
    For Each rA In Range(.Address).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Offset(rA.Rows.Count, 2).Resize(1, 2).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
    Next rA
  End With
End Sub

I have another data where I just need to sum columns C and D. What lines should I delete?

 

[leviackerman13]

Looks like you have changed columns. So this should do both jobs if student names are in column B.

Sub Row_Numbers_and_Totals()
  Dim rA As Range
 
  With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Name"",""Count"",N(R[-1]C)+1))"
    .Value = .Value
    For Each rA In Range(.Address).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Offset(rA.Rows.Count, 2).Resize(1, 2).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
    Next rA
  End With
End Sub

I have another data where I just need to sum columns C and D. What lines should I delete?

Don't mind this. I was able to work this out. My last task is to add a border from A2 to D5 and A8 to D13 and etc. Can you also help me with this? Below is what I wanted to do.

 

[Peter_SSs]

Assuming column A does not contain formulas, try ..

Sub Add_Borders()
  Dim rA As Range
  
  For Each rA In Columns("A").SpecialCells(xlConstants).Areas
    With rA.Resize(, 4)
      .BorderAround xlContinuous
      .Borders(xlInsideVertical).LineStyle = xlContinuous
      .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    End With
  Next rA
End Sub