Adding data onto an existing table with macro

Question

A project file has specific data in certain cells. I retrieve this data with ExecuteExcel4Macro(). Next, I want to put this dat in a table. is use;

Index = Application.ActiveSheet.Range("A10000").End(xlUp).Row + 1

to find the first empty row. This works perfectly as long as my data isn't in a table format. If it is, it adds in the first row after the table range. I think End(xlUp) cannot recognize an empty row in a table. But im not sure. For more context, I use the following code to put the data in my workbook. (simplified)

'Array of data I want in my table
Dim ref(15) As String

Index = Application.ActiveSheet.Range("A10000").End(xlUp).Row + 1

'GetValue is the ExecuteExcel4Macro() function, this works fine.
'I am using a for each loop to go through all the files to extract data
Application.ActiveSheet.Cells(Index, 1).Formula = GetValue(Path, file, ref(0))
Application.ActiveSheet.Cells(Index, 2).Formula = GetValue(Path, file, ref(1))

If needed I can elaborate further

SJR SJR · Accepted Answer · 2018-07-13T11:34:32

If you can't get rid of the empty rows, you could try using Find, viz

Sub x()

Dim Index As Long

Index = ActiveSheet.ListObjects("Table1").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

End Sub

Adding data onto an existing table with macro

1 Answers