array to select & delete columns

MR3

Board Regular
Joined
Jun 10, 2008
Messages
175
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,

 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try

Code:
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
 
Upvote 0
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...
Code:
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...
Code:
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
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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...

ndcruz21 said:
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)...
Code:
' 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
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top