Auto Serial Number in a Cell

[arijitirf]

Hi!
I have been using a code below, that is working flawlessly. I want data in D to be updated if I remove data from Column F to O.

Suppose, there is data in F3, G3, J3, K3, N3 & O3 and after execute the code data will populate in D3. Now if I delete all the data from F3 and O3 and run the code then D3 will not populate anything since there is no data b/w Column F and O (in the below code, data in D3 is still showing after run the code though there is no data b/w Column F and O)

Sub PopulateColumn()

Dim fr as Long
Dim lr As Long
Dim r As Long
Dim c As Long
Dim ct As Long
Dim str As String

Application.ScreenUpdating = False

' Set first and last rows to loop through
fr = 3
lr = 503

' Loop through all rows
For r = fr To lr
' Reset counter and string variable
ct = 0
str = ""
' Loop through columns F (6) to O (15)
For c = 6 To 15
' Check to see if it is a non-blank value
If Cells(r, c).Value <> "" Then
' Add one to counter
ct = ct + 1
' Build on to string
str = str & ct & ". " & Cells(r, c).Value & Chr(10)
End If
Next c
' Put result in column D
If Len(str) > 0 Then
Cells(r, "D").Value = Left(str, Len(str) - 1)
End If
Next r

Application.ScreenUpdating = True

End Sub

Requesting help.

Thanks in advance

 

[kvsrinivasamurthy]

Have you seen post#9

 

[myall_blues]

No changes. Same loop is delayed the process.

I don’t understand what you mean by ‘same loop is delayed the process’.

 

[kvsrinivasamurthy]

Edit Macro as in post#3.
Insert this worksheet event code for each sheet.
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F3:O503")) Is Nothing Then
Application.EnableEvents = False
Run "PopulateColumn"
Application.EnableEvents = True
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[NateSC]

No changes. Same loop is delayed the process.

You said the original ran fine. The only thing I added was an ELSE branch to the existing IF statement right before the NEXT r. This should not have changed the speed drastically. I will look for additional optimization.

 

[arijitirf]

If that's the case I think you need the Worksheet_Change event, which triggers whenever any cell in the worksheet is changed. The Worksheet_SelectionChange event triggers when a cell is selected.
In any event it can't be just a Sub - it needs to specifically be that event in the relevant sheet.

Following incorporates modification by @NateSC

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range
    Dim fr As Long
    Dim lr As Long
    Dim r As Long
    Dim c As Long
    Dim ct As Long
    Dim str As String
    Set MyRange = Range("F3:O503")
    If Not Intersect(Target, MyRange) Is Nothing Then
 
        Application.ScreenUpdating = False
     
        ' Set first and last rows to loop through
        fr = 3
        lr = 503
     
        ' Loop through all rows
        For r = fr To lr
            ' Reset counter and string variable
            ct = 0
            str = ""
            ' Loop through columns F (6) to O (15)
            For c = 6 To 15
                ' Check to see if it is a non-blank value
                If Cells(r, c).Value <> "" Then
                    ' Add one to counter
                    ct = ct + 1
                    ' Build on to string
                    str = str & ct & ". " & Cells(r, c).Value & Chr(10)
                End If
            Next c
            ' Put result in column D
            If Len(str) > 0 Then
                Cells(r, "D").Value = Left(str, Len(str) - 1)
            Else
                Cells(r, "D").Value = ""
            End If
        Next r
     
        Application.ScreenUpdating = True
     
    End If
End Sub

No changes. Same loop is delayed the process.

Can you post a sample file in some website and give link here.
Also I think F to O there are formulas and the output varies when changes are made in other cells.

Have you seen post#9

Yes, I have seen your post. Since my office laptop runs with company policy, I neither can attach XL2BB nor I can attach my original file to any cloud base storage. Sorry for that.

 

[arijitirf]

You said the original ran fine. The only thing I added was an ELSE branch to the existing IF statement right before the NEXT r. This should not have changed the speed drastically. I will look for additional optimization.

After adding the ELSE branch with

Cells(r, "D").Value = ""

the cursor cannot move to the another cell until the loop fr to lr is completed. I want it to be used under Worksheet_SelectionChange() since column F to O is changing based on input sourced from other sheet.

 

[NateSC]

After adding the ELSE branch with

the cursor cannot move to the another cell until the loop fr to lr is completed. I want it to be used under Worksheet_SelectionChange() since column F to O is changing based on input sourced from other sheet.

The ELSE branch shouldn't cause this problem. It doesn't change the behavior in any way. It only changes what is written to the cell.

 

[arijitirf]

The ELSE branch shouldn't cause this problem. It doesn't change the behavior in any way. It only changes what is written to the cell.

I am trying to attach a sample excel file using my home pc. Kindly allow me some time. Thank you and grateful for taking interest in my topic.

 

[arijitirf]

Auto Serial.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O
3	1. Swapnil 2. Raju 3. Saheb 4. Sayan 5. Monojit 6. Utsab 7. Priyank 8. Jaison 9. Sriram		Swapnil	Raju	Saheb	Sayan		Monojit	Utsab	Priyank	Jaison	Sriram
4
5
6	1. Swapnil 2. Raju 3. Saheb 4. Monojit 5. Utsab 6. Jaison 7. Sriram		Swapnil	Raju	Saheb			Monojit	Utsab		Jaison	Sriram
7
8	1. Sayan 2. Rahul 3. Monojit 4. Utsab 5. Jaison 6. Sriram					Sayan	Rahul	Monojit	Utsab		Jaison	Sriram
9
10
11
12	1. Swapnil 2. Raju 3. Sayan 4. Rahul 5. Utsab 6. Priyank 7. Sriram		Swapnil	Raju		Sayan	Rahul		Utsab	Priyank		Sriram
13
14
15	1. Swapnil 2. Sayan 3. Rahul 4. Monojit 5. Utsab 6. Priyank		Swapnil			Sayan	Rahul	Monojit	Utsab	Priyank
16
17
18
19	1. Swapnil 2. Raju 3. Saheb 4. Jaison 5. Sriram		Swapnil	Raju	Saheb						Jaison	Sriram
Sheet1

 

[arijitirf]

Auto Serial.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O
3	1. Swapnil 2. Raju 3. Saheb 4. Sayan 5. Monojit 6. Utsab 7. Priyank 8. Jaison 9. Sriram		Swapnil	Raju	Saheb	Sayan		Monojit	Utsab	Priyank	Jaison	Sriram
4
5
6	1. Swapnil 2. Raju 3. Saheb 4. Monojit 5. Utsab 6. Jaison 7. Sriram		Swapnil	Raju	Saheb			Monojit	Utsab		Jaison	Sriram
7
8	1. Sayan 2. Rahul 3. Monojit 4. Utsab 5. Jaison 6. Sriram					Sayan	Rahul	Monojit	Utsab		Jaison	Sriram
9
10
11
12	1. Swapnil 2. Raju 3. Sayan 4. Rahul 5. Utsab 6. Priyank 7. Sriram		Swapnil	Raju		Sayan	Rahul		Utsab	Priyank		Sriram
13
14
15	1. Swapnil 2. Sayan 3. Rahul 4. Monojit 5. Utsab 6. Priyank		Swapnil			Sayan	Rahul	Monojit	Utsab	Priyank
16
17
18
19	1. Swapnil 2. Raju 3. Saheb 4. Jaison 5. Sriram		Swapnil	Raju	Saheb						Jaison	Sriram
Sheet1

In this file your code is not taking that long time what I have experienced in my office file. May be that file is too big (almost 3 mb and other vba code is executing under Worksheet_Selection Change() Sub. Is there any other alternate to run the code smoothly without adding that ELSE Branch. Because that ELSE Branch is slowing down the process in my office file.