Incrementing Column A Values

Shbby · Jun 29th 2021

I have been striving to create a solution that is Increment the Number. Where from Column B data is start that is row 18 so code will copy value from the Column A row 17 to the last row of Column C with incrementing the number.

I'm hoping you can help me with.

Data

Result will be like this in Column A

Code

Sub IncrmentNumber()

Dim ws          As Worksheet, LRow As Long, r As Long
Set ws = Sheet1
Application.ScreenUpdating = False
LastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

For r = 2 To LastRow

If ws.Cells(r, "C") <> "" Then

ws.Cells(r - 1, "A").Value = ws.Cells(r, "A").Value + 1

End If
Next

Application.ScreenUpdating = True
End Sub

Display More

rollis13 · Jun 29th 2021

If I correctly understood the request, have a try with these modifications:

Code

Option Explicit
Sub IncrmentNumber()
    Dim ws As Worksheet, LRow As Long, r As Long
    Dim FirstRow, LastRow                         '<=added
    Set ws = Sheet1
    Application.ScreenUpdating = False
    FirstRow = ws.Range("B2").End(xlDown).Row     '<=added
    LastRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row '<= changed
    For r = FirstRow To LastRow
        If ws.Cells(r, "C") <> "" Then
            ws.Cells(r, "A").Value = ws.Cells(r - 1, "A").Value + 1 '<= changed
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Display More

**royUK** · Jun 30th 2021

Why do you need VBA for this? It is so simple to do without code!

Shbby · Jul 1st 2021

Thank you its working great. royUK Yes it is so simple without VBA but if i use the formula there are approximately 500K rows which will definitely increase the file size using formula which makes it slow.

rollis13 · Jul 1st 2021

Glad I was able to help.

Incrementing Column A Values

Participate now!

Share

Tags