Adding conditional column title - VBA

[ana_c]

Hi,
I'm new to VBA and would greatly appreciate your help with this query

I have a sheet that looks like this

I would like to add column titles in the highlighted cells based on the first left column that is not blank. For e.g. D1 will have the title -> C2+"_1" a.k.a "Column2_1" and cell E1 will be "Colum2_2".
Similarly in cell O1, it will be "Column5_1", P1="Column5_2" etc.

In total I have over 100 columns to go through and apply the same formula, however since these are header titles, we can restrict it to row 1.

Final desired output -

My data starts in B1

Thanks

 

[ana_c]

Surely that would be column XFD?

Can I safely use row 2 to determine the last column that does have data and stop there?
Or might row 2 have last data in column BV but row 10 might go further, say to column CA?

yeah, row 2 might have last data in column BV but row 10 can have data further down

 

[Peter_SSs]

yeah, row 2 might have last data in column BV but row 10 can have data further down

And so you would want the headings to go to whatever column that data further down is in? That is, the furthest right column that has data anywhere?

 

[ana_c]

YEah that's correct

 

[Peter_SSs]

OK, try

Sub Add_Titles_v3()
  Dim c As Range
  Dim Bits As Variant, Suff As Variant
  Dim LastCol As Long
  
  LastCol = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  For Each c In Rows(1).Resize(, LastCol).SpecialCells(xlBlanks)
    Bits = Split(c.Offset(, -1).Value, "_")
    Suff = Bits(UBound(Bits))
    If UBound(Bits) = 0 Or Not IsNumeric(Suff) Then
      c.Value = c.Offset(, -1).Value & "_1"
    Else
      Bits(UBound(Bits)) = vbNullString
      c.Value = Join(Bits, "_") & Suff + 1
    End If
  Next c
End Sub

 

[ana_c]

Amazing, this works great

Many thanks Peter

 

[Peter_SSs]

You're welcome. Glad we got there in the end.