Delete all columns except ones containing X value

Question

I am trying to delete all columns except names currently the code below deletes names.

Private Sub CommandButton2_Click()

Dim i As Integer
Dim A As Range

 For i = 100 To 1 Step -1
 Set A = Cells(1, i).Find(What:="Names", LookIn:=xlValues)
 
 If Not A Is Nothing Then A.EntireColumn.Delete
 Next i
End Sub

Updated Code:

Private Sub CommandButton1_Click()

Dim i As Integer
Dim A As Range


 If Cells(1, i) <> "Names" Then
 
 If A Is Nothing Then A.EntireColumn.Delete
 End If
End Sub

You don't need to loop if using `Find`. Change your If instead to `If A Is Nothing Then`. — SJR, Mar 01 '21 at 15:29
@SJR does that not only mean then if There is nothing in row 1 delete instead I want to delete columns that have data? — Ranjar, Mar 01 '21 at 15:37
@BigBen The look in value does this as the look at is defined by cells(1[row] and i [column]) then what the value is im looking to find in this case names, however i want to find it and delete everything but names — Ranjar, Mar 01 '21 at 15:39
You should *always* specify the `What`, `LookIn`, and `LookAt` parameters of `Range.Find`. See the Remarks section in the [docs](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find). — BigBen, Mar 01 '21 at 15:39
`If Not A Is Nothing` means that "Names" *has* been found so that column will be deleted. If you want to keep the columns with "Names", then remove the `Not`, as identified by @SJR in the first comment. — BigBen, Mar 01 '21 at 15:43
@SJR Yes exacttly that and ive changed teh code but keep getting a run time error application defined or object defined error — Ranjar, Mar 01 '21 at 15:52
@SJR Ive dropped the loop but not working, I have put updated and original code in my post now as well — Ranjar, Mar 01 '21 at 15:58

score 1 · Accepted Answer · answered Mar 01 '21 at 16:04

1

I cannot believe there is not a more efficient way, but this should work.

To allow for the Names column being anywhere, first I move it to column 1 before deleting everything else. The 100 is hard-coded so you may wish to change that.

Private Sub CommandButton1_Click()

Dim i As Long 'better than integer
Dim A As Range

Set A = Rows(1).Find(What:="Names", LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

If Not A Is Nothing Then 'Names found
    Columns(A.Column).Cut
    Range("A1").Insert
    Range("B1").Resize(, 99).EntireColumn.Delete
Else
    Range("A1").Resize(, 100).EntireColumn.Delete 'delete all columns as Names not found
End If

End Sub

answered Mar 01 '21 at 16:04

SJR

22,986
6
18
26

Ok wow, so that works, does seem like a convoluted way to go about it, does the find method allow for multiple columns to be kept? or would I have to create an A, B, C sort of thing ? – Ranjar Mar 01 '21 at 16:08
Yes, you can use `FindNext`, store the found columns and then move them all to the beginning. Or some sort of reverse Intersect - there is probably code for that out there. – SJR Mar 01 '21 at 16:09
@Ranjar - fyi this might give you some ideas how to rearrange columns via `Application.Index()` function [Delete column and change position of two columns](https://stackoverflow.com/questions/62664086/delete-an-array-column-and-change-position-of-two-columns/62666087#62666087); of course you'd have to try some code of your own to findt the wanted columns. – T.M. Mar 01 '21 at 20:57

Delete all columns except ones containing X value

1 Answers1