How to Copy and Paste Cell Data Including Row Height and Column Width in Excel

If we want to copy a cell with different size to another normal size cell, its content will not be fully displayed in the destination with the desired size. Copying data with cell dimensions, including row heights and column widths, will ensure that the pasted cells are the same size as the copied cells, which will help us check the data in the spreadsheet for a good and consistent table structure.

Read More: Copy and Paste Cell or Row Heights to Another in Excel

If we copy the cell only by traditional “Copy “and “Paste” features, only the cell value is copied to the destination.

How to Copy and Paste Cell Data Including Row Height and Column Width in Excel 1.png

Our goal is to copy and paste calls that include not only their cell values, but also cell dimensions, such as row height and column width. This post will show you the way to copy and paste cell with its row height and column width in Excel.

Copy and Paste Cell Row Height and Column Width via Excel Paste Special Feature and Format Painter Feature

In Excel worksheet, to copy and paste cell data including the cell row height and column width, you can follow these steps:

1. Select the cell or range of cells that you want to copy.

2. Right-click on the selected cell, select “Copy” in the menu. Or press “Ctrl + C” on your keyboard.

3. Select the destination cell or range of cells where you want to paste the copied data.

4. Right-click on the destination cell or range of cells and select “Paste Special” -> “Keep Source Column Widths” from the “Paste Special” menu.

How to Copy and Paste Cell Data Including Row Height and Column Width in Excel 2.png

After this step, column widths are applied to the selected cell or range of cells properly.

How to Copy and Paste Cell Data Including Row Height and Column Width in Excel 3.png

The following are the steps for copying the row height:

5. Click on the row number of the row that you want to copy the row height.

How to Copy and Paste Cell Data Including Row Height and Column Width in Excel 4.png

6. Click the Format Painter on the Home tab of the ribbon.

How to Copy and Paste Cell Data Including Row Height and Column Width in Excel 5.png

7. Click on the row number of the row that you want to apply the row height. Format Paint will apply the row height to the selected row.

How to Copy and Paste Cell Data Including Row Height and Column Width in Excel 6.png

Note: After copying the row height with the Format Paint tool, the font size is reduced to 12, which is the default font size when opening an Excel worksheet. This is because the default font size for row 1 is still 12 (cell A1 has a font size of 18), and when copying the row formatting, the default font size is applied to the selected row as well. So it is better to copy the row height first, and then copy the cell values while maintaining the

How to Use QueryTables in Excel VBA

This post will introduce you to the concept of QueryTables in Excel VBA. QueryTables is an object that allow you to import data from external sources, such as databases, web pages, and text files, directly into your Excel workbook.

In this post, we will discuss how to create, refresh, and delete QueryTables using VBA code, and we’ll provide examples of how to use QueryTables with different types of data sources.

Creating a New QueryTable

If you want to create a new QueryTable, you can use the Add method of the QueryTables collection of a Worksheet object.

The Add method has several parameters that you can use to customize the QueryTable, such as the Connection parameter, which specifies the data source, and the Destination parameter, which specifies the range of cells where the data should be imported.

Here’s an example of how to create a new QueryTable in VBA:

Sub ImportData_excelgeek()
    Dim myquerytable  As QueryTable
    Set myquerytable = ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password", Destination:=Range("A1"))
End Sub
How to use QueryTables in Excel VBA 1.png

In this example, the Connection parameter specifies an ODBC connection to a database, and the Destination parameter specifies that the imported data should be placed in cell A1 of the current worksheet.

Specifying Data Source

The Connection parameter is used to specify the data source for the QueryTable. The syntax of the Connection parameter depends on the type of data source you are using.

If you want to import data from a web page, you can use the following connection parameter:

Connection:="URL;http://www.excelgeek.net/mycontent.html"

If you want to import data from a text file, you can use the following Connection syntax:

Connection:="TEXT;C:\Folder\MyTextFile.txt"

To import data from a database, you can use the following syntax:

Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password"

In this example, the ODBC connection uses the Data Source Name (DSN) ” DatabaseName “, the username ” Username “, and the password ” Password “.

Refreshing the QueryTable

Once you have created a QueryTable, you can refresh it by calling the Refresh method of the QueryTable object.

Here’s an example of how to refresh a QueryTable:

Sub RefreshData_excelgeek()
    Dim myquerytable  As QueryTable
    Set myquerytable = ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password", Destination:=Range("A1"))
    myquerytable.Refresh
End Sub
How to use QueryTables in Excel VBA 2.png

In this example, the RefreshData_excelgeek subroutine gets a reference to the first QueryTable in the active sheet’s QueryTables collection, and then calls the Refresh method of the QueryTable to refresh the data.

Cleaning up QueryTable

After you have finished using a QueryTable, you should clean it up by calling the Delete method of the QueryTable object.

Here’s an example of how to delete a QueryTable:

Sub Cleanup_Data_excelgeek()
    Dim myquerytable  As QueryTable
    Set myquerytable = ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password", Destination:=Range("A1"))
    myquerytable.Refresh
    myquerytable.Delete
End Sub

In this example, the Cleanup_Data_excelgeek subroutine gets a reference to the first QueryTable in the active sheet’s QueryTables collection, and then calls the Delete method of the QueryTable to delete it.

How to use QueryTables in Excel VBA 3.png