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

 

[Joe4]

Welcome to the Board!

Will it always be on exactly 3 columns of data?

 

[johnnyL]

How about:

Sub test()
'
    Dim ColumnNumber                As Long
    Dim LastColumnNumberUsedInSheet As Long
    Dim LastRowInColumn             As Long
'
    LastColumnNumberUsedInSheet = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column       ' Get last used column number used in the sheet
'
    For ColumnNumber = LastColumnNumberUsedInSheet To 1 Step -1                                         ' Loop backwards through columns of the sheet
        LastRowInColumn = Cells(Rows.Count, ColumnNumber).End(xlUp).Row                                 '   Save last used row of the column into LastRowInColumn
'
        With Range(Cells(1, ColumnNumber + 1), Cells(LastRowInColumn, ColumnNumber + 1))
            .Value = Evaluate("Row(" & .Address & ")")                                                  '       Set the row # for the column
        End With
'
        If ColumnNumber > 1 Then Columns(ColumnNumber).EntireColumn.Insert                              '       If ColumnNumber > 1 then insert a column to be numbered
    Next
End Sub

 

[Peter_SSs]

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

 

[Oraekene]

Welcome to the Board!

Will it always be on exactly 3 columns of data?

Sorry i'm just responding now. Thought i'd get email notifications of the responses. It wouldn't always be 3 columns, more columns would be added to the sheet over time. It could extend to 100+ columns in the future. So i'd be grateful if the formula can work with a dynamic range

 

[Peter_SSs]

So i'd be grateful if the formula can work with a dynamic range

So, have you tried the suggestions that have been made?

Thought i'd get email notifications of the responses

You should get an email after the first response is made after you last visited the thread. So you should have received an email after post #2, but not after posts 3 & 4. If you did not get that then you should check your spam folder and settings.

 

[Oraekene]

How about:

Sub test()
'
    Dim ColumnNumber                As Long
    Dim LastColumnNumberUsedInSheet As Long
    Dim LastRowInColumn             As Long
'
    LastColumnNumberUsedInSheet = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column       ' Get last used column number used in the sheet
'
    For ColumnNumber = LastColumnNumberUsedInSheet To 1 Step -1                                         ' Loop backwards through columns of the sheet
        LastRowInColumn = Cells(Rows.Count, ColumnNumber).End(xlUp).Row                                 '   Save last used row of the column into LastRowInColumn
'
        With Range(Cells(1, ColumnNumber + 1), Cells(LastRowInColumn, ColumnNumber + 1))
            .Value = Evaluate("Row(" & .Address & ")")                                                  '       Set the row # for the column
        End With
'
        If ColumnNumber > 1 Then Columns(ColumnNumber).EntireColumn.Insert                              '       If ColumnNumber > 1 then insert a column to be numbered
    Next
End Sub

Thank you very much for the response. Tried this, gave me an error 1004. Attached here is an excel file of the same sample data as in the pics to help guide: Sample Data file.

The third and fourth sheets are to give an example of how the sheet could be expanded (hence why the vba needs to work with a dynamic column). Again, grateful for the responses

 

[Oraekene]

So, have you tried the suggestions that have been made?


You should get an email after the first response is made after you last visited the thread. So you should have received an email after post #2, but not after posts 3 & 4. If you did not get that then you should check your spam folder and settings.

yes i got the emails, thank you. Its how i found your response. I attach here a sample sheet of the data, to help: Sample Data Sheet. Again, grateful for your response

 

[Oraekene]

So, have you tried the suggestions that have been made?


You should get an email after the first response is made after you last visited the thread. So you should have received an email after post #2, but not after posts 3 & 4. If you did not get that then you should check your spam folder and settings.

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

Thank you. I tried this, it gave me an error. I'll soon share screenshots of the error. Attached here is the sample data file: Sample Data Sheet

 

[Oraekene]

How about:

Sub test()
'
    Dim ColumnNumber                As Long
    Dim LastColumnNumberUsedInSheet As Long
    Dim LastRowInColumn             As Long
'
    LastColumnNumberUsedInSheet = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column       ' Get last used column number used in the sheet
'
    For ColumnNumber = LastColumnNumberUsedInSheet To 1 Step -1                                         ' Loop backwards through columns of the sheet
        LastRowInColumn = Cells(Rows.Count, ColumnNumber).End(xlUp).Row                                 '   Save last used row of the column into LastRowInColumn
'
        With Range(Cells(1, ColumnNumber + 1), Cells(LastRowInColumn, ColumnNumber + 1))
            .Value = Evaluate("Row(" & .Address & ")")                                                  '       Set the row # for the column
        End With
'
        If ColumnNumber > 1 Then Columns(ColumnNumber).EntireColumn.Insert                              '       If ColumnNumber > 1 then insert a column to be numbered
    Next
End Sub

This was the error i got with this