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 amp metapage rawMetadata amp 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

User · Answer

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

User · Answer

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

User · Answer

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   gt  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

[excel] How to delete specific columns with VBA?

Examples related to excel

Examples related to vba