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
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
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]
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.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'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.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)?
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)...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.
' 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