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

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
176
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.

VBA Code:
'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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,572
Office Version
  1. 2007
Platform
  1. Windows
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

Board Regular
Joined
Jul 11, 2019
Messages
176
Thanks - I "fixed" the issue by just moving my macro to run before the table formatting procedure which fixed it.
 
Solution

Forum statistics

Threads
1,141,075
Messages
5,704,162
Members
421,331
Latest member
imdumb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top