how use this procedure to delete specific columns based on headers

[Hasson]

Hi experts
can any body guide me how use this procedure to delete specific columns based on headers?
the columns should delete them ("UNIT PRICE", "ORDERS")

With Sheets("Result" )
            For Each e In Array("UNIT PRICE", "ORDERS")
            .Rows(1).Find(e, , , 1).EntireColumn.Delete
            Next
End with

actually I don't understand this line

  .Rows(1).Find(e, , , 1).EntireColumn.Delete

of course the e is the headers names but the others I don't understand it. just I've found in specifc code and I try implement for my file but does'nt work . it seems to delete the last column .
any help guys?

 

[Micron]

Any line that starts with a dot like .Rows means that whatever is being referenced in the line is a member/child (for lack of a better term) of the object that follows the With statement. If not done in a With block you'd have to type
Sheets("Result").Rows(1).Find...
which doesn't seem all that important here. However, a With block is usually created because there's going to be several things you're going to want to do to or with the members of the main object, like this:

Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
   .Title = "Select File"
   .AllowMultiSelect = False
   .InitialFileName = "C:\Users\Micron\Documents\Excel\A*"
   .Filters.Add "EXCEL", "*.xlsm", 1
End With

I cannot tell why your code doesn't work for you - it works for me as you have it or without the With block as follows (I used sheet "6" and it deleted columns N and O where I put your headers and some data):

For Each e In Array("UNIT PRICE", "ORDERS")
     Sheets("6").Rows(1).Find(e, , , 1).EntireColumn.Delete
Next

I guess the issue isn't the space at the end of your sheet name in code if it deletes the last column. Sorry, I'm not an Excel vba guru and don't know what else to suggest - except maybe to be sure you don't have two column headers with the same value. I think that would cause the last one with the same name to be deleted.

 

[jolivanes]

Sub Maybe()
Dim myArr, i As Long
myArr = Array("UNIT PRICE", "ORDERS")
    With Sheets("Result")
        For i = LBound(myArr) To UBound(myArr)
            .Columns(.Rows(1).Find(myArr(i)).Column).EntireColumn.Delete
        Next i
    End With
End Sub

 

[Hasson]

@Micron
actually I have three sheets (result1,result2,result3) and this part of the code

n = n + 1
            If Not Evaluate("isref('result" & n & "'!a1)") Then
                Sheets.Add(, Sheets(Sheets.Count)).Name = "Resut" & n
            End If
            With Sheets("Result" & n).Cells(1).Resize(dic.Count + 1, UBound(a, 2) - 2)
            For Each e In Array("UNIT PRICE", "ORDERS")
            .Rows(1).Find(e, , , 1).EntireColumn.Delete
            Next
.CurrentRegion.Borders.LineStyle = xlNone
                .CurrentRegion.ClearContents
                .NumberFormat = ""
                .Rows(1).Font.Bold = True
                .Value = a
                .Sort .Cells(1, 2), , , , , , , 1
                .Columns(1).Offset(1).Resize(dic.Count) = Evaluate("row(1:" & dic.Count & ")")
                .Borders.Weight = 2
                .Columns("i:j").NumberFormatLocal = "$#,##0.00"
                .HorizontalAlignment = xlCenter
                
            End With
            dic.RemoveAll
        End If
    Next

so if it's not enough I will post the whole code

 

[Hasson]

@jolivanes thanks I tested in one sheet it works perfectly , but I have two things how can implement for three sheets RESULT1, RESULT2,RESULT3 . also I note after delete the columns will show "error object variable or with block variable not set" . it seems beacuse of the columns are not existed any more. how can I get rid of this error?

 

[jolivanes]

You mentioned that you don't understand this.

.Rows(1).Find(e, , , 1).EntireColumn.Delete

Do you understand the code you posted in Post #4.
You have spelling mistakes in the little bit you posted in Post #4 also.
The error you described in your last post is not from the code I supplied and the following code will not generate this error on it's own with your supplied data.
Check for spelling mistakes, leading and/or trailing spaces in sheet names etc.
In the future in your first Post, mention everything that affects writng a macro. Not like here where it was never mentioned that it is for multiple sheets untill your last Post.

Sub Maybe_2()
Dim headerArr, sheetArr, i As Long, j As Long
headerArr = Array("UNIT PRICE", "ORDERS")
sheetArr = Array("RESULT1", "RESULT2", "RESULT3")
For j = LBound(sheetArr) To UBound(sheetArr)
    With Sheets(sheetArr(j))
        For i = LBound(headerArr) To UBound(headerArr)
            .Columns(.Rows(1).Find(headerArr(i)).Column).EntireColumn.Delete
        Next i
    End With
Next j
End Sub

 

[jolivanes]

BTW, if the macro can't find the data (headers or sheet names) you mentioned, it will result in the error you alluded to in your Post #5.

 

[Hasson]

BTW, if the macro can't find the data (headers or sheet names) you mentioned, it will result in the error you alluded to in your Post #5.

I know . so there is any procedure by message box to informe me? like this "please make sure, the headers" UNIT PRICE", " ORDER" are not existed at all" instead shows error.
and sorry iI'm not clear from the beginning. I thought the matter will be ok based on OP.