VBA error not selection and deleting columns

Question

Today with VBA I was trying to come with something that would modify several .xlsx files in a folder and then would copy and paste the data to another worksheet. In one of the steps, I am trying to select and delete specific columns from the other Excel file based on headers' names in VBA. The columns I would like to remove are all between the headers named "Unsubscribed" and "Webinar Question 1". The issue is that when it come to this part, it gives me an error 91:

With Worksheets("Sheet0").Range("A1:BB1")
    Range(Worksheets("Sheet0").Range("A1:BB1").Find(What:="Unsubscribed", LookIn:=xlValues, MatchCase:=False).Offset(0, 1), _
        Cells(Worksheets("Sheet0").Cells(Worksheets("Sheet0").Rows.Count, "A").End(xlUp).Row, _
        Worksheets("Sheet0").Range("A1:BB1").Find(What:="Webinar Question 1", LookIn:=xlValues, MatchCase:=False).Offset(0, -1).Column) _
    ).Select
End With

The whole VBA is:

    Private Sub CommandButton1_Click()

    Dim my_files As String
    Dim folder_path As String
    Dim wb As Workbook, lRow As Long
    Dim ws As Worksheet, LR As Long
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long

    folder_path = "C:\Users\XXXX\Desktop\"
    my_files = Dir(folder_path & "\*.xlsx")

Do While my_files <> vbNullString

Set wb = Workbooks.Open(folder_path & "\" & my_files)
Set ws = wb.Worksheets("Sheet0")


'INSERT COLUMN AND PASTE FILE NAME IN ALL ROWS UNTIL LAST ROW
LR = ws.Range("B9").End(xlDown).Row
ws.Columns(1).Select
ActiveCell.EntireColumn.Insert
ws.Range("A8").Value = "Title"
ws.Range("A9:A" & LR).Value = ActiveWorkbook.Name


'INSERT COLUMN AND PASTE CELL A5 VALUE IN ALL ROWS UNTIL LAST ROW
ws.Columns(2).Select
ActiveCell.EntireColumn.Insert
ws.Range("B8").Value = "Duration"
ws.Range("B9:B" & LR).Value = ws.Range("E5").Value


'DELETE A1:A7 ROWS
ws.Range("A1:A7").EntireRow.Delete


'DELETE ORGANIZATION COLUMN
Dim rng As Range
With Worksheets("Sheet0").Range("A1:BB1")
    Set rng = Worksheets("Sheet0").Range("A1:BB1").Find(What:="Organization", _
        lookat:=xlWhole, MatchCase:=False)
    Do While Not rng Is Nothing
        rng.EntireColumn.Delete
        Set rng = .FindNext
    Loop
End With

'SELECT AND DELETE ALL COLUMNS BETWEEN UNSUBSCRIBED AND WEBINAR QUESTION 1
With Worksheets("Sheet0").Range("A1:BB1")
    Range(Worksheets("Sheet0").Range("A1:BB1").Find(What:="Unsubscribed", LookIn:=xlValues, MatchCase:=False).Offset(0, 1), _
        Cells(Worksheets("Sheet0").Cells(Worksheets("Sheet0").Rows.Count, "A").End(xlUp).Row, _
        Worksheets("Sheet0").Range("A1:BB1").Find(What:="Webinar Question 1", LookIn:=xlValues, MatchCase:=False).Offset(0, -1).Column) _
    ).Select.Delete
End With

Set wsCopy = Worksheets("Sheet0")
Set wsDest = Workbooks("Book1.xlsm").Worksheets("Sheet2")

lCopyLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

wsCopy.Range("A2:BB1" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)

wb.Close True
my_files = Dir()

Loop

MsgBox ("All Files Are Updated")

End Sub

For clarification, the worksheet where I have the VBA has Sheet1 and Sheet2, and the file that i am modifying has Sheet0.

What am I missing? Thank you very much and have a nice day.

You are missing that the `With Worksheets("Sheet0").Range("A1:BB1")` has no effect because there is no expression inside that block that would start with a `.`, that `Range()` accepts two arguments instead of three, that `Find` may return `Nothing` and cause error 91, that despite what the comment claims there is no `Delete` called on the range, that you [don't need to select](https://stackoverflow.com/q/10714251/11683) a range to delete it, and that "it gives me an error" is not a valid description of the problem. — GSerg, Jan 13 '21 at 22:01
All problems listed above remain, plus the new one, that you cannot call `Delete` on the result of a `Select` because `Select` returns `True` or `False`. If the first error that you see is error 91, then see https://stackoverflow.com/q/29102052/11683, but there will be the other problems to fix once you've fixed this one. — GSerg, Jan 13 '21 at 22:14
I will check that link. So far that section is the only one with error. The rest works correct. — Alber, Jan 13 '21 at 22:46

VBA error not selection and deleting columns

0 Answers0