How to delete specific columns with VBA?

Question

I am trying to delete multiple columns in VBA for Excel. I downloaded 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 Illinois Estimate |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 macro:

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

I keep getting an error:

Run-time 13: type mismatch

Any suggestions?

Jan

No. I am not familar with macros. is there a link where you can show me how to do this? – Jan

it says cannot excute code in break mode – Jan

Answer 1

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.

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

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.

Frailty

Thank You! Do you know of any resources where I can learn more about vba? – Jan

Personally my main resources are this website and Google. I've never taken any formal classes or tutorials on this so I wouldn't know what to recommend in that area unfortunately. – Frailty

I don't think this would work if two consecutive columns happen to contain the forbidden string. Any time a column gets deleted the next column doesn't get checked due to the column shifting into the "i" location. Example. Columns c & d (3 & 4) both have the string. i=3. No problem, it catches the string and deleted the column shifting the former "D" column (4) into the position of C (3). Then next i =4. It doesn't check column 3 again to see that the string. It gets skipped. – Hustings

A bigger question is, why go to all the trouble of creating a dynamic solution with a hard coded criteria in the first place? If you want to do that, use an input box to at least make it actually dynamic. Otherwise, hope you spell that string correctly with all the correct capitalisation. Just saying. – Hustings

You declaration of nLastCol is dimmed as Variant FYI – Kamalakamaria

Answer 2

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:

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

Just change the column letters to match your needs.

Hustings

Answer 3

This works for me (as an example):

ColumnsDelete ("B:D,F:F,K:S,U:Y")

Sub ColumnsDelete(Cols$)  
  Range(Cols$).Select  
  Selection.Delete Shift:=xlToLeft  
End Sub

Dortch

Answer 4

To answer the question How to delete specific columns in vba for excel. I use Array as below.

sub del_col()

dim myarray as variant
dim i as integer

myarray = Array(10, 9, 8)'Descending to Ascending
For i = LBound(myarray) To UBound(myarray)
    ActiveSheet.Columns(myarray(i)).EntireColumn.Delete
Next i

end sub

Coretta