How to select row tables / full table?

Question

Setup: I have an excel document with a data entry form, the data for this form is entered into a table, which makes it easy to enter several rows of data. At least I thought so.

So, now I'm trying to select a table to insert its data into the appropriate places. My question, I think, is this: I select one row of the table at a time or the whole table and process each row separately. And how to do it?

I tried Sheets("Form").Range("dataForm[#ALL]").Select and several variants of it, and no one worked.

If I select the table as a whole, I need to be able to process each row separately, and if I select each row separately, I will need to be able to start at the top of the table, since the data should be in order.

Any ideas?

EDit: add details. I have the form indicated above, and its data must be inserted into different tables, depending on the value of certain cells in the form. For convenience of discussion, we will call this cell type; it has three possible values, as defined in the drop-down list. These values ​​are income, expense and transfer. Based on these values, we decide which table to add data to. Income from the table of income to expenses, etc.

So what I'm trying to do is select as many rows as there are and insert them into the correct table. Sorting is a bit more complicated than I explained, but if I can figure out the initial sort, then it will just sort it a few more times.

Answer 1

This should help answer your questions.

 Sub TableStuff() Dim lo As Excel.ListObject Dim loRow As Excel.ListRow Dim i As Long Set lo = ActiveSheet.ListObjects(1) With lo 'this is the address of the whole table Debug.Print .Range.Address For i = 1 To 10 Set loRow = .ListRows.Add(i) loRow.Range.Cells(1).Value = "test" & i Next i Debug.Print .Range.Address 'address of data rows Debug.Print .DataBodyRange.Address End With End Sub 

I have two posts on my spreadsheet blog. recent may also provide some ideas.

EDIT: based on the comments below and edit the OP:

This assumes two tables on Activesheet, tblSource and tblIncome. It filters the source table into Revenue, copies copies of the visible rows, and pastes them at the end of tblIncome. Finally, it deletes the original lines (all but one).

You need to add a loop to make it work for two other categories:

 Sub MoveTableStuff() Dim loSource As Excel.ListObject Dim loTarget As Excel.ListObject Dim SourceDataRowsCount As Long Dim TargetDataRowsCount As Long Set loSource = ActiveSheet.ListObjects("tblSource") Set loTarget = ActiveSheet.ListObjects("tblIncome") With loSource .Range.AutoFilter Field:=1, Criteria1:="income" SourceDataRowsCount = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Count End With With loTarget TargetDataRowsCount = .DataBodyRange.Rows.Count .Resize .Range.Resize(.Range.Rows.Count + SourceDataRowsCount, .Range.Columns.Count) loSource.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy .DataBodyRange.Cells(TargetDataRowsCount + 1, 1).PasteSpecial (xlPasteValues) Application.CutCopyMode = False End With With loSource .Range.AutoFilter .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete End With End Sub 

Answer 2

If you have already given a name to your table, I have a function to get the full range of data:

 Public Function GetTableByName(ByVal ws As Worksheet, ByVal tbName As String) As Range Dim lObj As ListObject For Each lObj In ws.ListObjects If Trim(UCase(lObj.Name)) = Trim(UCase(tbName)) Then Set GetTableByName = lObj.DataBodyRange Exit Function End If Next lObj End Function