array to select & delete columns

[MR3]

i have an array of data in which i would like to encorporate a script to Select All of the specific columns that are NOT in the array to delete them For example,

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    Columns(Range("A" & i).Value).Delete
Next i
End Sub

 

[Rick Rothstein]

Your question is not clear to me... did you want to delete Columns A, C, F, X, AB, DE or are you trying to delete everything except those columns of information? I'm assuming you meant the latter.

Are you willing to use column numbers instead of column letters in your list in Column A? If so, here is a very fast non-looping macro that will do what you asked...

Sub KeepSpecifiedRows()
  Dim NewColumnNumberOrder As String, LastRowOnSheet As Long, LastRowColumnA, Cols As Variant
  LastRowColumnA = Cells(Rows.Count, "A").End(xlUp).Row
  LastRowOnSheet = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Cols = Application.Index(Cells, Evaluate("Row(1:" & LastRowOnSheet & ")"), WorksheetFunction.Transpose(Range("A1:A" & LastRowColumnA)))
  Application.ScreenUpdating = False
  Cells.Clear
  Range("A1").Resize(LastRowOnSheet, LastRowColumnA) = Cols
  Application.ScreenUpdating = True
End Sub

If you would rather continue using letters to identify the columns to keep, then we need to introduce a short loop to convert the letters to number for use in the Index function...

Sub KeepSpecifiedRows()
  Dim NewColumnNumberOrder As String, LastRowOnSheet As Long, LastRowColumnA As Long, NL As Long, ColNumberLetters As Variant, Cols As Variant
  LastRowColumnA = Cells(Rows.Count, "A").End(xlUp).Row
  LastRowOnSheet = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  ColNumberLetters = WorksheetFunction.Transpose(Range("A1:A" & LastRowColumnA))
  For NL = LBound(ColNumberLetters) To UBound(ColNumberLetters)
    ColNumberLetters(NL) = Columns(ColNumberLetters(NL)).Column
  Next
  Cols = Application.Index(Cells, Evaluate("Row(1:" & LastRowOnSheet & ")"), ColNumberLetters)
  Application.ScreenUpdating = False
  Cells.Clear
  Range("A1").Resize(LastRowOnSheet, LastRowColumnA) = Cols
  Application.ScreenUpdating = True
End Sub

 

[MR3]

i think the code posted just deletes just the columns in my array, but i want to delete the columns that are not in the array.
I was referring to VoG's post

 

[MR3]

Your question is not clear to me... did you want to delete Columns A, C, F, X, AB, DE or are you trying to delete everything except those columns of information? I'm assuming you meant the latter.

[/CODE]

Thanks Rick! i want to delete everything except those columns of information, which is right that latter of your assumptions. I guess with code would work loop or not loop. I am fine with converting the column letters to numbers.

I guess another side question that I have is in Excel, how come if you type 1 2 and then select and drag it will continue the count but if you do the same with letters A, B, C, in different cells, then select them and drag it just repeats rather than extending the alphabet to match the column headers (default)?

 

[Rick Rothstein]

Thanks Rick! i want to delete everything except those columns of information, which is right that latter of your assumptions. I guess with code would work loop or not loop. I am fine with converting the column letters to numbers.

You can continue to use column letters, just use that second macro if you do. Don't worry about the loop that gets introduced as it is quite quick and will not affect the overall speed of the code in any noticeable way.

I guess another side question that I have is in Excel, how come if you type 1 2 and then select and drag it will continue the count but if you do the same with letters A, B, C, in different cells, then select them and drag it just repeats rather than extending the alphabet to match the column headers (default)?

I'm only guessing here, but the drag/increment feature works with numerical values and while each letter has a numerical ASCII value associated with it (so one would think the programmers for Excel could have incremented that), the question of what comes after "Z" would need to be resolved. The natural incremented character would be the lefthand square bracket ([), but my guess is you would like it to be the letters AA. They probably figured it was easier to make it work with real numbers and avoid the problem. Again, remember, I'm only guessing here.

 

[Rick Rothstein]

The OP thanked me via a private message and also mention to me that his list of columns to keep are on a different worksheet than the one where the columns to be deleted are located. Here is the relevant part of his message and my response to it...

The other thing is my array is on a different sheet than the actual data that i am looking at

so like

sh1 = "Sheets1"
sh2 = "Sheets2" sheets2 is where my data will be deleteed from, i forgot to mention this in my post.

Here are both of my macro versions (see comment at top for which is which) modified to allow you to individually specify the sheet names for the "keep columns" list and the data sheet (see the comments at the end of each Set statement)...

' Using column NUMBERS in Column A
Sub KeepSpecifiedRows()
  Dim NewColumnNumberOrder As String, LastRowOnSheet As Long, LastRowColumnA, Cols As Variant, Sh1 As Worksheet, Sh2 As Worksheet
  Set Sh1 = Worksheets("Sheet1")  'Sheet with column letters to keep
  Set Sh2 = Worksheets("Sheet2")  'Sheet with columns that are going to be deleted
  LastRowColumnA = Sh1.Cells(Sh1.Rows.Count, "A").End(xlUp).Row
  LastRowOnSheet = Sh2.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Cols = Application.Index(Sh2.Cells, Evaluate("Row(1:" & LastRowOnSheet & ")"), WorksheetFunction.Transpose(Sh1.Range("A1:A" & LastRowColumnA)))
  Application.ScreenUpdating = False
  Sh2.Cells.Clear
  Sh2.Range("A1").Resize(LastRowOnSheet, LastRowColumnA) = Cols
  Application.ScreenUpdating = True
End Sub
 
' Using column LETTERS in Column A
Sub KeepSpecifiedRows()
  Dim NewColumnNumberOrder As String, LastRowOnSheet As Long, LastRowColumnA As Long, NL As Long, ColNumberLetters As Variant, Cols As Variant, Sh1 As Worksheet, Sh2 As Worksheet
  Set Sh1 = Worksheets("Sheet1")  'Sheet with column letters to keep
  Set Sh2 = Worksheets("Sheet2")  'Sheet with columns that are going to be deleted
  LastRowColumnA = Sh1.Cells(Sh1.Rows.Count, "A").End(xlUp).Row
  LastRowOnSheet = Sh2.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  ColNumberLetters = WorksheetFunction.Transpose(Sh1.Range("A1:A" & LastRowColumnA))
  For NL = LBound(ColNumberLetters) To UBound(ColNumberLetters)
    ColNumberLetters(NL) = Columns(ColNumberLetters(NL)).Column
  Next
  Cols = Application.Index(Cells, Evaluate("Row(1:" & LastRowOnSheet & ")"), ColNumberLetters)
  Application.ScreenUpdating = False
  Sh2.Cells.Clear
  Sh2.Range("A1").Resize(LastRowOnSheet, LastRowColumnA) = Cols
  Application.ScreenUpdating = True
End Sub