In this article, we will learn about how to delete empty columns through VBA in Microsoft Excel. We will delete the columns which are completely empty, it means if there is.

Access provides several ways to add or remove the columns in a datasheet. You can use Datasheet view to add or remove columns and set the data types for those columns. You can also add fields from a task pane, or you can open the table that underlies the datasheet and add a field in Design view. This topic explains how to use each method.

For more information about creating and using datasheets, see the articles Create a form using the Datasheet tool and Working with datasheets.

What do you want to do?

Understand columns in datasheets

A datasheet is the visual representation of the information contained in a database table, or of the results returned by a query. A column in a datasheet represents the same thing as a field in a database table. When you add or remove a column from a datasheet, you add or remove a field from the table that underlies the datasheet. If that field contains data, you also eliminate that information.

Working with columns

Access makes it faster and easier to add or remove a table field because you can perform those tasks in Datasheet view. By default, all tables in Datasheet view now contain a blank column labeled Click to Add. To add a column, you enter data into the first blank cell beneath that column header. You can also paste one or more pieces of data into the blank column. You no longer need to use Design view to add or remove columns, although you can if you want.

In addition, you no longer have to set the more common data types for the new column. By default, the fields in a database table must contain specific types of data, such as text, dates and times, numbers, and so on. Typically, you set data types when you design the tables for a database. However, Access now infers most data types when you first enter data in a new column. For example, if you enter a name in the first field in your new table, Access sets that field to the Text data type. If you paste a date, Access sets the field to the Date/Time data type, and so on. If you paste a mix of data, such as postal codes from several countries/regions, Access selects the data type that does the best job of preserving the information — usually, the Text data type. For more information, see the section Understand how Access assigns data types as you enter information, later in this article.

For more information about setting data types, see the article Data types for Access desktop databases. If you are not familiar with design rules for relational databases, see the article Database design basics.

The steps in the following sections explain how to add and remove columns in datasheets by using Datasheet view and Design view.

Add a column by using Datasheet view

The steps in this section explain how to use Datasheet view to add a column to a datasheet, give the column a name, and enter data. As you proceed, remember that following these steps adds and changes a field in the table that underlies your datasheet.

  1. In the Navigation Pane, locate and double-click the table to which you want to add the field.
    Access opens the table in Datasheet view.
  2. Scroll to the right or left side of the datasheet (depending on your Windows Regional and Language settings), and locate the blank column.
    By default, the words Click to Add appear in the column header of the blank column.
  3. Enter some data in the first blank row beneath the header.
    -or-
    Paste one or more pieces of data into the field, starting with the first blank cell.
    Save your changes.
    Based on the type of data you enter, Access sets a data type for the field. For example, if you enter a name, Access sets the data type to Text.
    For more information about how Access sets data types, see the section Understand how Access assigns data types as you enter information. For information about setting data types manually, see Set the data types that Datasheet view does not infer, also later in this article.
  4. Double-click the column header and enter a name for the new field.
    -or-
    Right-click the column header and click Rename Field on the shortcut menu. Then type a name for the field.
  5. Save your changes.

Remove a column by using Datasheet view

Before you remove a column from a datasheet, remember several important facts:

Vba Delete Column

Remove the column in Datasheet view

  1. Right-click the header row of the column that you want to remove.
  2. Click Delete Field on the shortcut menu.
  3. Click Yes to confirm the deletion.
  4. Save your changes.

Add a column by using Design view

If you are familiar with earlier versions of Access, you probably have some experience using Design view. Design view provides greater flexibility than Datasheet view because you can set all available data types, and you can create a lookup field, all without having to leave Design view.

Add the column in Design view

  1. In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
    -or-
    On the Access status bar, click Design View.
  2. In the Field Name column, select a blank row and type a name for the new field.
  3. In the Data Type column, next to your new field name, select a data type for the new column.
  4. Save your changes.

Remove a column by using Design view

Range

The rules for deleting a column in Datasheet view also apply to working in Design view: If you delete a column, you lose all the data in that column. In addition, before you can delete a primary key or a lookup field, you must first delete the relationships for those fields.

For more information, see the articles Database design basics, Add or change a table’s primary key in Access, and Create or delete a multivalued field.

Remove the column in Design view

  1. In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
    -or-
    On the Access status bar, click Design View.
  2. Select the field (the row) that you want to delete.
  3. On the Design tab, in the Tools group, click Delete Rows.
    -or-
    Press DELETE.
  4. Save your changes.

Understand how Access assigns data types as you enter information

When you create a blank datasheet, Access assigns a data type to each field when you first enter data in that field. The following table lists the various types of data that you can enter and the data type that Access applies to each one.

Note: You cannot set the Attachment or OLE Object data types by entering data in a field, and you cannot enable support for rich-text editing by entering data in a field.

The following table shows how Access sets the data type for a field when you enter data in Datasheet view.

If you enter:
Access creates a field with a data type of:
John
Short Text (Text)
A block of text or text and numbers longer than 256 characters.
Long Text (Memo)
Note: You cannot use Datasheet view to enable rich-text formatting. Also, if you enable a property called Append Only for the Memo field, then by default, Access hides any text when you place your cursor in that field.
For information about enabling rich-text formatting, see the article Create or delete a Rich Text field.
http://www.contoso.com
Access recognizes the following Internet protocols: http, ftp, gopher, wais, file, https, mhtml, mailto, msn, news, nntp, midi, cid, prospero, telnet, rlogin, tn3270, pnm, mms, outlook.
Note: For Access to recognize the protocol and set the Hyperlink data type, you must follow the protocol with a non-whitespace character.
Hyperlink
50000
Number, Long Integer
50,000
Number, Long Integer
50,000.99
Number, Double
50000.389
Number, Double
12/31/2019
The date and time formats specified in your Windows Regional and Language settings control how Access formats Date/Time data.
Date/Time
December 31, 2019
Note: You must enter or paste more than the name of day for Access to infer the Date/Time data type. For example, if you enter 'Tuesday,' Access selects the Text data type. For Access to infer the Date/Time data type, you must enter a month in addition to the day.
Date/Time
10:50:23
Date/Time
10:50 am
Date/Time
17:50
Date/Time
$12.50
Access recognizes the currency symbol specified in your Windows Regional and Language settings.
Currency
21.75
Number, Double
123.00%
Number, Double
3.46E+03
Number, Double

Set the data types that Datasheet view does not infer

By default, you cannot perform some tasks by using Datasheet view:

The steps in this section explain how to perform those tasks.

Set an Attachment data types

  1. If it isn't already selected, select the blank column (the one labeled Click to Add) in your datasheet.
  2. In the drop-down list select Attachment.
  3. Save your changes.

Add a Long Text (Memo) field to a table with rich-text editing

  1. If it isn't already selected, select the blank column (the one labeled Click to Add) in your datasheet.
  2. In the drop-down list select Rich Text.
  3. Save your changes.

Convert a column into a lookup field

By default, you cannot use Datasheet view to convert a new column into a lookup field. If you are new to Access, a lookup field displays data from another source — either a table or a list of items — in a list. By default, Access uses a drop-down list to display lookup data, but can also use a list box (a list that doesn't open or close) in a form.

You can create two kinds of lookup fields — table-based lists and value lists. A table-based list uses a query to retrieve data from another table, and a value list displays a set of hard-coded values. The following procedure explains how to create both types of lists.

Add a lookup field

  1. In the Navigation Pane, right-click the table that underlies your new datasheet and click Design View.
    -or-
    On the Access status bar, click Design View.
    The table opens in Design view.
  2. Select the field that you want to convert.
    -or-
    Select a blank row in the Field Name column and enter a name for the new field.
  3. On the Design tab, in the Tools group, click Lookup Column.
    -or-
    In Design view, in the Data Type column, click the down arrow and select Lookup Wizard.
    The Lookup Wizard starts.
  4. Do one of the following:
  5. Create a value-based list
    1. Click I will type in the values that I want, and then click Next.
    2. In the Number of columns box, enter the number of columns that you want to appear in your list, then go to the first blank cell and enter a value.
      When you enter your first value, another blank cell appears below the current cell.
    3. When you finish entering your first value, use the TAB or down-arrow keys to move focus to the next cell, and enter a second value.
    4. Repeat steps 2 and 3 until you finish creating your list, and then click Next.
    5. Optionally, enter a name for the new field, and then click Finish.
I am trying to delete multiple columns in vba for excel. I download data from the Illinois Statistical analysis center about drug arrest. http://www.icjia.org/public/sac/index.cfm?metasection=forms&metapage=rawMetadata&k=170
Each of the columns I want to delete are 3 columns apart from each other.
For example:
Adams County Illinois Champaign County IllinoisEstimate |percent | Percent Margin of Error |Estimate Margin| Estimate|Percent|Percent Margin of Error
D|E|F|G|H|I|J
I just want to delete all the columns the say Percent Margin of Error
Here is my micro:
I keep getting a Run-time '13: type mismatch error
Any suggestions?
Ron Rosenfeld
25.5k4 gold badges17 silver badges41 bronze badges
Zaynaib GiwaZaynaib Giwa
1,0855 gold badges14 silver badges20 bronze badges

3 Answers

You say you want to delete any column with the title 'Percent Margin of Error' so let's try to make this dynamic instead of naming columns directly.
With this you won't need to worry about where you data is pasted/imported to, as long as the column headers are in the first row.
EDIT: And if your headers aren't in the first row, it would be a really simple change. In this part of the code: If InStr(1, wsCurrent.Cells(1, i).Value, 'Percent Margin of Error', vbTextCompare) change the '1' in Cells(1, i) to whatever row your headers are in.
EDIT 2: Changed the For section of the code to account for completely empty columns.
BobbitWormJoeBobbitWormJoe
You were just missing the second half of the column statement telling it to remove the entire column, since most normal Ranges start with a Column Letter, it was looking for a number and didn't get one. The ':' gets the whole column, or row.
I think what you were looking for in your Range was this:
Just change the column letters to match your needs.
peegepeege
2,3241 gold badge6 silver badges23 bronze badges
To answer the questionHow to delete specific columns in vba for excel.I use Array as below.
ArunArun

Not the answer you're looking for? Browse other questions tagged excelvbaexcel-vba or ask your own question.