Resolve Extremely Slow Pasting Into Excel Tables
Stop The Madness!
One thing that has always annoyed me with Excel Tables, is how slow it can be to paste large amounts of data into them. This seemingly stems from an inefficiency with how an Excel Table Object resizes itself (one cell at a time). This inefficiency has lingered for years, and after waiting minutes to paste new data into tables as I update monthly files, I finally set out to solve this issue on my own.
How To Manually Resolve This Issue
Before I was able to come up with a programmatic way to solve this issue, I had to understand the root cause. What ultimately speeds up pasting data into an Excel Table, is expanding the table size prior to pasting the data. This requires figuring out the size of your new data set and adding/removing rows/columns to get your destination table the same size as your source data.
This solution is not difficult to do, but it can become cumbersome if you are updating a lot of Tables every month (like I currently am). So let’s look at how I created a VBA macro to solve my issue, so now I only have to click a button to quickly paste my data into my Tables.
How To Automatically Resolve This Issue
What Does This Code Do?
This VBA code is meant to replace the need of hitting your paste button. Prior to running the code, you will want to make sure you have copied your data to the Clipboard and have at least one cell selected in your destination Table (the table you wish to resize and paste).
You will first be prompted to indicate if your copied data includes your Header Row or if it only contains data within the data. After you have made your indication, the code will proceed to either shrink or expand your table and paste the values into the Table object.
How Does This Code Work?
1. Test To Ensure There Is Something In The Clipboard
Before we get too far into the code, we want to make sure there is actually data copied to Excel’s Clipboard! We start the code off by checking to see if anything was copied (xlCopy) or cut (xlCut) to the Clipboard. If this test fails, we notify the user and exit out of the code.
2. Determine The “ActiveTable”
The next test we want to do before getting too far is ensuring our user has selected a cell within a table. Luckily, I had created this little functionality a while back and was able to grab the code from my VBA with Tables Guide. If the user happens to not have selected a valid location for us to locate their desired destination Table object, then we must exit the subroutine.
3. Read The Copied Data From The Clipboard
To access the Clipboard, we are going to need to utilize the GetFromClipboard function. The problem is, this function is part of the MS Forms 2.0 Object Library. This library is typically deactivated unless you have a userform created in your project or you have manually gone into the available Libraries list and activated it (VBE > Tools > References…).
In an effort to bypass manually activating this library, I am using the CreateObject function and the reference code (that crazy long code) to call and activate the library why the VBA is running. After that piece of code has been executed, we now have access to the entire MS Forms 2.0 library, including the Clipboard object.
4. Calculate How Many Rows/Columns Are Needed
Next, we need to analyze the data we pulled from the Clipboard. When you copy cells to the Clipboard, the data is separated by Tabs (vbTab) and Carriage Returns (vbCrLf). What we can do is count how many carriage returns are residing within the stored data in the Clipboard and that will let us know how many rows we have. We can also count how many tabs are located within the first row of the Clipboard’s data set to determine the column count.
5. Resize The Table
Now we are to the time-saving part! Since we have now figured out how many rows of data were copied to the Clipboard, we can begin to resize the table before we paste our data.
First, we’ll need to figure out if we need to expand or reduce the size of the table. You can simply do this by comparing the ActiveTable’s row count to the Clipboard’s. After that is determined, the code will either prepare to delete table rows or add them with the Resize function.
The one tricky part about this codes is the order in which the resizing process takes place. If you delete rows or columns, Excel will automatically clear the clipboard leaving you with nothing to paste. Hence, why it was important to handle the minimizing the tables footprint separately from any expansion activities.
6. Paste The Data (Coded as Paste Values Only)
Finally, after the Excel Table is resized to our likely, we can paste in the data. I’ve chosen to paste values only, but you can modify the code to use whatever PasteSpecial type you’d like.
Sub ResizeAndPasteToTable()
'PURPOSE: Resize ActiveTable Based on Copied Data and PasteValues Only
'SOURCE: www.TheSpreadsheetGuru.com
Dim myClipboard As Object
Dim ActiveTable As ListObject
Dim TableSize As Range
Dim TableName As String
Dim ClipboardData As String
Dim RowCount As Long
Dim ColumnCount As Long
Dim TableRows As Long
Dim TableColumns As Long
Dim RowDifference As Long
Dim ColumnDifference As Long
Dim UserAnswer As Long
Dim HeaderAdjuster As Integer
Dim x As Integer
'Ensure Data is copied
If Application.CutCopyMode <> xlCopy And Application.CutCopyMode <> xlCut Then
MsgBox "Please copy data to the clipboard before running this."
Exit Sub
End If
'Determine if ActiveCell is inside a Table
On Error GoTo NoTableSelected
TableName = ActiveCell.ListObject.Name
Set ActiveTable = ActiveSheet.ListObjects(TableName)
On Error GoTo 0
'Ask User if they Copied Table Headings
UserAnswer = MsgBox("Does your copied data include table headings?", vbYesNoCancel)
Select Case UserAnswer
Case vbYes: HeaderAdjuster = 0
Case vbNo: HeaderAdjuster = 1
Case vbCancel: Exit Sub
End Select
'Turn Off ScreenUpdating
Application.ScreenUpdating = False
'Create Data Object using MS Forms 2.0 Object Library (Late Binding)
Set myClipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
'Gather how many rows/columns to resize Table (based on Copied Range)
myClipboard.GetFromClipboard
ClipboardData = myClipboard.GetText
RowCount = UBound(Split(ClipboardData, vbCrLf))
ColumnCount = UBound(Split(Split(ClipboardData, vbCrLf)(0), vbTab)) + 1
If RowCount = 0 Then Exit Sub
'Determine difference between copied data and table row count
TableRows = ActiveTable.DataBodyRange.Rows.Count
TableColumns = ActiveTable.DataBodyRange.Columns.Count
RowDifference = RowCount - TableRows
ColumnDifference = ColumnCount - TableColumns
'Resize ActiveTable Rows (If Necessary)
If RowDifference > 0 Then
'Store desired new size to a Range Variable
Set TableSize = Range(ActiveTable.Name & "[#All]"). _
Resize(RowCount + HeaderAdjuster, TableColumns)
'Resize Table
ActiveTable.Resize TableSize
End If
'Resize ActiveTable Rows (If Necessary)
If ColumnDifference > 0 Then
'Store desired new size to a Range Variable
Set TableSize = Range(ActiveTable.Name & "[#All]"). _
Resize(ActiveTable.Range.Rows.Count, ColumnCount)
'Resize Table
ActiveTable.Resize TableSize
End If
'Paste Data into Table (Values Only)
If UserAnswer = vbYes Then
ActiveTable.Range.Cells(1, 1).PasteSpecial xlPasteValues
Else
ActiveTable.DataBodyRange.Cells(1, 1).PasteSpecial xlPasteValues
End If
'Reduce Table Row Count (if necessary)
If RowDifference < 0 Then
TableRows = ActiveTable.DataBodyRange.Rows.Count
ActiveTable.DataBodyRange.Rows(TableRows + HeaderAdjuster + RowDifference & ":" & TableRows).Delete
End If
'Reduce Table Column Count (if necessary)
If ColumnDifference < 0 Then
TableColumns = ActiveTable.DataBodyRange.Columns.Count
For x = 1 To -ColumnDifference
ActiveTable.Range.Columns(TableColumns + ColumnDifference + 1).Delete
Next x
End If
'Clear Clipboard
Application.CutCopyMode = msoFalse
Exit Sub
'ERROR HANDLERS
NoTableSelected:
MsgBox "There is no Table currently selected!", vbCritical
End SubI Hope This Helped!
This was a pain I was dealing with for far too long. And in my humble opinion, this is a HUGE problem that Microsoft needs to fix. Many people praise the versatility that Excel Tables give you, but if they are a pain to use with large data sets (and our data needs are only growing), why go through the hassle?
What are your thoughts on this? Let me know in the comments section below!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.