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?
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.
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.
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
Source: Stackoverflow.com