Excel VBA, is it possible to select column/row range within a table to paste information into, and use torow for it?

Question

I am trying to get data from my data imput sheet into my record keeping table, so I am trying to do VBA code to
1. create new row
2. paste the data in there as value and not formula

I created something that does work but it is very clanky and can only be applied to one specific data set, and if I want to add more information to my input sheet, or reorder my data, I would need to change the code again, I feel like its not well optimised and quite useless in this way to be frank


Dim newRow As ListRow
 
Set newRow = ActiveWorkbook.ActiveSheet.ListObjects("Table1").ListRows.Add


With newRow
    .Range(1) = ActiveCell
    .Range(2) = ActiveCell.Offset(0, 1)
    .Range(3) = ActiveCell.Offset(1, 0)
    .Range(4) = ActiveCell.Offset(1, 1)
End With

  
End Sub

I tried to do something like below but it is not working at all

Range("newRow")= Application.WorksheetFunction.TOROW (Range(ActiveCell, ActiveCell.Offset(2, 2)))

Ideally I would like to have code to add row to a table in the same sheet (in any sheet I use), take information from any ctrl a area of data from ActiveCell and fill it only with data given (e.g. my table has 15 rows, and my box has only 6 cells, I would want it to fill only the 6 cells and leave the others empty (they have excel native formulas) )

Is that possible? How to do it, or at least allow multiple selection range in "With .range"?

Not all worksheet functions are callable via VBA. You could write a simple function in VBA which mimics TOROW, but that will only support resizing the source range, and not re-ordering etc. — Tim Williams
– Tim Williams, Commented Nov 11 at 22:19
@timwilliams Is there any way of combining VBA and torow? If not then ignoring torow, is there any way to copy values from one range starting at active cell to a range in a specified table? so far all I found only does cell by cell — Tom Smith
– Tom Smith, Commented Nov 11 at 22:24

Tim Williams · Accepted Answer · 2025-11-11 23:16:17Z

1

You can mimic TOROW() in VBA:

Sub AddRowFromActiveCell()

    Dim newRow As ListRow, arr
     
    Set newRow = ActiveSheet.ListObjects("Table1").ListRows.Add
    
    arr = RangetoToRow2(ActiveCell.CurrentRegion) 'get the array
    
    newRow.Range.cells(1).Resize(1, UBound(arr)).Value = arr 'fill [part of] the row
    
End Sub

'return values from `rng` as a 1D array
Function RangetoToRow(rng As Range)
    Dim arr(), r As Long, c As Long, i As Long
    ReDim arr(1 To rng.cells.Count)
    'fill the array
    For r = 1 To rng.Rows.Count
        For c = 1 To rng.Columns.Count
            i = i + 1
            arr(i) = rng.cells(r, c).Value
        Next c
    Next r
    RangetoToRow = arr
End Function

'return values from `rng` as a 1D array
Function RangetoToRow2(rng As Range)
    With rng
        RangetoToRow2 = .Parent.Evaluate("TOROW(" & .Address & ")")
    End With
End Function

edited Nov 11 at 23:16

answered Nov 11 at 22:32

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Smith Nov 11 at 23:00

You are a blessing I don't know how to thank you enough. Could I ask two last small bits? 1. Is there a way to only input range 1-4 (for e.g.) instead of the whole row? (In my record table the first few columns are user input but the rest are calculations based on those and I don't want to keep refreshing calculations). Intuitively I want to do something like newRow.Range("1:4").Value = RangetoToRow(ActiveCell.Resize(2, 2)) But it does not do that at all. 2. If I want to change rangetorow from the 2x2 to the entire box, would .Resize.CurrentRegion cause problems?

Tim Williams Nov 11 at 23:17

See the updates in AddRowFromActiveCell above - that will fill only the number of values found in the ActiveCell.CurrentRegion, starting from the first cell in the added table row.

Collectives™ on Stack Overflow

Excel VBA, is it possible to select column/row range within a table to paste information into, and use torow for it?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related