1

I have a code that will automatically fill sequential numbers depends on the range of data.

here's my code:

Dim Lst As Long
Lst = Range("B" & Rows.Count).End(xlUp).Row
With Range("A12")
    .Value = "1"
    .AutoFill Destination:=Range("A12").Resize(Lst - 11), Type:=xlFillSeries
End With

well it worked fine but I have this output. Last time I already asked on how to clear any duplicates.

here's the output of it when I apply the sequential numbers:

No |   Col 2   | Col 1
  1|  Person 1 | 1. Question 1
  2|           | 2. Question 2
  3|           | 3. Question 3
    -------------------------
  4|  Person 2 | 1. Question 1
  5|           | 2. Question 2
  6|           | 3. Question 3
    -------------------------
  7| Person 3  | 1. Question 1
  8|           | 2. Question 2
  9|           | 3. Question 3

I want to have it like this:

No |   Col 2   | Col 1
  1|  Person 1 | 1. Question 1
   |           | 2. Question 2
   |           | 3. Question 3
    -------------------------
  2|  Person 2 | 1. Question 1
   |           | 2. Question 2
   |           | 3. Question 3
    -------------------------
  3| Person 3  | 1. Question 1
   |           | 2. Question 2
   |           | 3. Question 3

but I don't know how? or what will I add to the code. thanks in advance!!

0

You can achieve the desired output by finding out which row in Column B has a Person then use that row to insert the incremented value into Column A's respective row.

To learn more have a look through the below code reading the comments to give you tips on what the code is doing.

Sub OrderColumn()
    Dim cellsInRange As Range
    Dim selectedRange As Range

    Set selectedRange = Range("B12:B" & LastRow(2)) 'Use LastRow(column) function to find the last row with a value in "column".
    i = 0 'This is our value that increments.

    For Each cellsInRange In selectedRange.Cells
        If Range("B" & (cellsInRange.Row)).Value = "" Then
            'Do nothing.
        Else
            i = i + 1 'Increment i by 1.
            Range("A" & (cellsInRange.Row)).Value = i 'Add i as the new cell value.
        End If
    Next cellsInRange

End Sub
Function LastRow(column As Long)
    LastRow = Cells(Rows.Count, column).End(xlUp).Row
End Function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.