So I am having issues defining a dynamic table range using the index function, I think the problem is arising because the table I am attempting to create a dynamic range for has cells inside that are part of an array but I am not sure.
I am currently using this function which works in the fact that it names H17 as the bottom right cell with text in the range;
=INDEX(C2:H1000,(COUNTA(C2:C103)-COUNTBLANK(C2:C103)),COUNTA(C2:H2))
(Also worth noting that when I change the above function to this it ceases to work for some reason? it throws up a #VALUE error?
=INDEX(C2:H1000,(COUNTA(C2:C1000)-COUNTBLANK(C2:C1000)),COUNTA(C2:H2))
When I place the top left cell in beside like this into the name manager I cannot seem to get it to function the same way as I have another table to function inside of my VBA code, which just pastes the table into a word document?
=$C$2:INDEX(C2:H1000,(COUNTA(C2:C103)-COUNTBLANK(C2:C103)),COUNTA(C2:H2))
I also used the CountA function to try to count just the cells with the numbers in column C but that did not work using the function below:
=INDEX(C2:H1000,(COUNTA(C2:C1000)),COUNTA(C2:H2))
Code used to paste into word:
Set tbl = ThisWorkbook.Sheets(7).Range("SummaryTableTest")
'& ThisWorkbook.Sheets(7).Cells(ThisWorkbook.Sheets(7).Rows.Count, 2).End(xlUp).Row).Value
'.Copy
'Copy the range
tbl.Copy
'Pause the Excel app for one second
Application.Wait Now() + #12:00:01 AM#
'Paste the object (Ensuring that it will be paste from the correct clip board i.e. Excel)
With WordApp.Selection
WordDoc.Bookmarks("SummaryTable").Range.PasteExcelTable LinkedToExcel:=False, _
WordFormatting:=True, _
RTF:=False
End With
The spreadsheet looks like this:
