1

I have a dynamically generated spreadsheet which comes with some information I want to remove. I want to find a column with the header "WEI-21" and then delete all columns to the right, but I can't get my code to work.

Excel says SYNTAX ERROR, but it's not very descriptive beyond that.

Can anybody take a look and tell me what looks wrong?

Public Sub FindCol()

'Find Last Column
Dim lCol As Long
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Dim LastSamplePrepColumn As Range
Dim FirstAnalyticalColumn As Range
Dim Analytical As Range
Dim rngHeaders As Range

Set rngHeaders = Range("2:2")

Set LastSamplePrepColumn = rngHeaders.Find("WEI-21")
Set FirstAnalyticalColumn = LastSamplePrepColumn.Offset(0, 1)
Set Analytical = (FirstAnalyticalColumn:lCol) 'This is the line excel highlights re: SYNTAX ERROR

ActiveWorkbook.ActiveSheet.Range(Analytical).Value = TEST

End Sub

Update Re TedD's Comment: I now get a different error re Object or Application Defined error

Public Sub FindCol()

'Find Last Column
Dim lCol As Long
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Dim LastSamplePrepColumn As Range
Dim FirstAnalyticalColumn As Range
Dim Analytical As Range
Dim rngHeaders As Range

Set rngHeaders = Range("1:1")

Set LastSamplePrepColumn = rngHeaders.Find("WEI-21")
Set FirstAnalyticalColumn = LastSamplePrepColumn.Offset(0, 1)
Set Analytical = ActiveWorkbook.ActiveSheet.Range(FirstAnalyticalColumn, lCol) 'Application or Object Defined Error

ActiveWorkbook.ActiveSheet.Range(Analytical).Value = TEST

End Sub
  • What kind of object is expected to be generate when evaluating the expression (FirstAnalyticalColumn:lCol) ? My guess is a Range since you have Set it to a Range variable. The problem is there is no function call to return a Range object and the only potential operator, : is not actually an operator in VBA (in a formula, it is a From:To operator). To achieve the same result in VBA, use the two argument call to Range, Range(<From>,<To>). In this case Range(FirstAnalyticalColumn,lCol) . – Ted D. Mar 17 '19 at 3:13
  • @TedD. I gave that a go an now I'm getting a different error – FreeSoftwareServers Mar 17 '19 at 4:13
  • @FreeSoftwareServers, are you looking for Code to Delete all Columns which has Header other than WEI-22 ? – Rajesh S Mar 17 '19 at 9:37
  • The error is the second argument to Range. It is a long when it should be a Range or a String address of a cell. Make the second argument "A" & lcol or Cells(1, lcol) instead of lcol. – Ted D. Mar 17 '19 at 19:04
2

Stripped down to one statement.

Public Sub DeleteColumns()
  Range(Cells(1, Range("2:2").Find("WEI-21").Column + 1), _
    Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column)).EntireColumn.Delete
End Sub

Range on ActiveSheet

  • From: cell in row 1, the column after finding "WEI-21" (in row 2)
  • To: cell in row 1, the last column
  • This Range: EntireColumn Delete.
| improve this answer | |
1

With data like:

enter image description here

Running this:

Public Sub FindCol()


Dim lCol As Long, i As Long
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Dim LastSamplePrepColumn As Range

Dim rngHeaders As Range

Set rngHeaders = Range("1:1")

Set LastSamplePrepColumn = rngHeaders.Find("WEI-21")
i = LastSamplePrepColumn.Column + 1

Range(Cells(1, i), Cells(1, lCol)).EntireColumn.Delete

End Sub

will produce:

enter image description here

All columns to the right are deleted.

| improve this answer | |
0

Here is how I ended up doing it, this was before the posted answers but I didn't get a chance to post last night.

I also added some If/Then statements because I might have a different header that I may want to delete from the right of.

The logic is a bit weird in that if I have WEI-22 then I will have WEI-21 for sure and it's always to the left, so I check for WEI-21 then WEI-22. This also will allow for future additional If/then for more WEI-** statements.

'''''PUBLICVARS'''''
Public lCol As Variant
Public lRow As Variant
Public rngHeaders As Range
Public rngWEIHeader As Range
Public rngWEI21Header As Range
Public rngWEI22Header As Range
Public colLetter As Variant
Public lColLet As Variant
'''''ENDPUBVARS'''''

'''''MAINSUB'''''
'Delete Analtical Columns
Call NoAnalytical
End Sub
'''''ENDMAINSUB'''''
'''''FINDLAST'''''
Public Sub FindLast()

'Find Last Column in Number Form
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

'Find Last Column Letter
    lColLet = Split(Cells(1, lCol).Address, "$")(1)

'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

End Sub
'''''ENDFINDLAST'''''
'''''GETCOLLET'''''
Public Sub GetColLet(actc As Range)
colLetter = Split(actc.Address, "$")(1)
End Sub
'''''ENDGETCOLLET'''''
'''''FINDHEADERS'''''
Public Sub FindHeaders(HeaderCol)

Set rngHeaders = Range(HeaderCol)
Set rngWEIHeader = rngHeaders.Find("WEI-21") 'Shouldn't be needed!, need better way to find WO# or IF/WEI-25 Added
Set rngWEI21Header = rngHeaders.Find("WEI-21")
Set rngWEI22Header = rngHeaders.Find("WEI-22")

End Sub
'''''ENDFINDHEADERS'''''
'''''NOANALYTICAL'''''
Public Sub NoAnalytical()
Call FindLast 
Dim Analytical As Range

Call FindHeaders("2:2")
    If Not rngWEI21Header Is Nothing Then
        call GetColLet(rngWEI21Header)
    End If  
    If Not rngWEI22Header Is Nothing Then
        call GetColLet(rngWEI22Header)
    End If  
Dim AnaStartCol As Variant
Set Analytical = Range(colLetter & "1:" & lColLet & lRow)
Analytical.Offset(0, 1).Clear

End Sub
'''''ENDNOANALYTICAL'''''
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.