How to delete specific columns in vba for excel

Question

I am trying to delete multiple columns in vba for excel. I am downloading data from the Illinois Center for Statistical Analysis about drug seizure. http://www.icjia.org/public/sac/index.cfm?metasection=forms&metapage=rawMetadata&k=170

Each of the columns that I want to delete is three columns apart.

For instance:

Adams County Illinois Champaign County Illinois Rate |% | Percentage Margin Errors | Margin Estimation | Rating | Percentage | Percentage Margin Error

D | E | F | G | H | me | J

I just want to delete all columns, say Percent Margin of Error

Here is my micro:

Sub deleteCol() Columns("H,J").Delete End Sub 

I keep getting runtime error 13: type of mismatch

Any suggestions?

Answer 1

You say that you want to delete any column with the heading “Percentage of marginal error”, so try to make this dynamic rather than name the columns directly.

 Sub deleteCol() On Error Resume Next Dim wbCurrent As Workbook Dim wsCurrent As Worksheet Dim nLastCol, i As Integer Set wbCurrent = ActiveWorkbook Set wsCurrent = wbCurrent.ActiveSheet 'This next variable will get the column number of the very last column that has data in it, so we can use it in a loop later nLastCol = wsCurrent.Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 'This loop will go through each column header and delete the column if the header contains "Percent Margin of Error" For i = nLastCol To 1 Step -1 If InStr(1, wsCurrent.Cells(1, i).Value, "Percent Margin of Error", vbTextCompare) > 0 Then wsCurrent.Columns(i).Delete Shift:=xlShiftToLeft End If Next i End Sub 

You don’t need to worry about where you insert or import data, as long as the column headers are on the first row.

EDIT: And if your headings are not on the first line, this will be a very simple change. In this part of the code: If InStr(1, wsCurrent.Cells(1, i).Value, "Percent Margin of Error", vbTextCompare) change the value "1" in Cells(1, i) to any line that contains your headers.

EDIT 2: Changed the For section of the code to account for completely empty columns.

Answer 2

You simply did not have the second half of the column instruction, which said about deleting the entire column, since most normal ranges begin with the letter of the column, it looked for the number and did not receive it. ":" Gets the entire column or row.

I think you were looking in your range:

 Range("C:C,F:F,I:I,L:L,O:O,R:R").Delete 

Just change the letters of the columns to suit your needs.