How to loop adding a column with row numbers beside each column with data, in a dynamic range

[Oraekene]

Good day. Been on this for more than a week and can't find a solution. New to VBA. I have a data sheet with multiple columns of data. I would like to add, beside each column, a column with the row numbers of each column, and to do this through the range till it hits an empty column. Something like below. My data looks like the first pic. I'd like a VBA that loops through that and turns it into the second pic. Would be grateful for any response

 

[Oraekene]

Another option that you could consider

Sub AddRowNums()
  Dim c As Long
 
  Application.ScreenUpdating = False
  For c = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
    Columns(c + 1).Insert
    With Range(Cells(1, c), Cells(Rows.Count, c).End(xlUp)).Offset(, 1)
      .Cells(1).Value = 1
      .DataSeries
    End With
  Next c
  Application.ScreenUpdating = True
End Sub

This was the error i got

 

[johnnyL]

I tested the codes in post #3 & Post #4 with your 'input' sheet and both codes executed without error.

When you get the error, click the 'Debug' button and tell us which line i highlighted yellow.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:
How to loop adding a column with row numbers beside each column in a dynamic range in

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Oraekene]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:
How to loop adding a column with row numbers beside each column in a dynamic range in

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Was an error, my apologies

 

[Peter_SSs]

I tested the codes in post #3 & Post #4 with your 'input' sheet and both codes executed without error.

I agree with @johnnyL. Neither code errored for me either.
As requested, please click 'Debug' when you run each of our codes and tell us which line of our code showed as the error line.

 

[Oraekene]

This was the error i got with thisView attachment 74467

Sorry for the late response, been a bit busy. Thank you so much! Rechecked it again and it work! The forum allows me only mark 1 reply as the solution otherwise i'd have marked your response too. Again, thank you so much! I'm on the final part of the task. I need to get a sum of values for each unique item across multiple columns. So far what i've seen online is i need the combination of a sumif and index-match, but i have no idea how to write this in vba

 

[Oraekene]

I agree with @johnnyL. Neither code errored for me either.
As requested, please click 'Debug' when you run each of our codes and tell us which line of our code showed as the error line.

Sorry for the late response, been a bit busy. Thank you so much! Rechecked it again and it work! The forum allows me only mark 1 reply as the solution otherwise i'd have marked your response too. Again, thank you so much! I'm on the final part of the task. I need to get a sum of values for each unique item across multiple columns. So far what i've seen online is i need the combination of a sumif and index-match, but i have no idea how to write this in vba

 

[Oraekene]

This was the error i gotView attachment 74468

Sorry for the late response, been a bit busy. Thank you so much! Rechecked it again and it work! The forum allows me only mark 1 reply as the solution otherwise i'd have marked your response too. Again, thank you so much! I'm on the final part of the task. I need to get a sum of values for each unique item across multiple columns. So far what i've seen online is i need the combination of a sumif and index-match, but i have no idea how to write this in vba

 

[johnnyL]

Glad we could help.