Can I post an array into a spreadsheet but maintain the table format of the original data?

[TheRedCardinal]

Hi all,

My macro does the following:

1. Loads the content of 2 sets of data into an array
2. Loops through the arrays and compares various columns, and produces a third array with data that I want to output

Here is the code that outputs the array to the workbook.

'Populate Tax Code column into table

Set PasteRange = WS2.Cells(2, PasteCol).Resize(UBound(TaxArray), 1)
PasteRange = Application.Transpose(TaxArray)

The problem I have found is, the array is loaded into VBA using the "Worksheet.UsedRange" parameter because I need to capture everything. Everything consists of data in a table, and data underneath that table in "non-table" rows, all of which needs to go through the process.

So for example, if the table might occupy range A1:M220, but there is data in rows 224-230 and 233 - 245 that needs to be put through the data analysis.

But when I output the array, the whole range (A1:M245) becomes within the table.

Is there anyway to output the array with the equivalent of a PasteSpecial/Values only kind of command?

 

[DanteAmor]

So for example, if the table might occupy range A1:M220, but there is data in rows 224-230 and 233 - 245 that needs to be put through the data analysis.
But when I output the array, the whole range (A1:M245) becomes within the table.

Does it mean that rows 221,222,223,231,232 are filled with data?

You could put all your code here to try to reproduce it. And maybe a sample of data for testing.

 

[TheRedCardinal]

Thanks - I "fixed" the issue by just moving my macro to run before the table formatting procedure which fixed it.

 

[DanteAmor]

Nice to hear that