+ Reply to Thread
Results 1 to 4 of 4

compile error expected line number statement end statement

  1. #1
    Forum Contributor
    Join Date
    08-07-2013
    Location
    mo
    MS-Off Ver
    Excel 2010
    Posts
    453

    compile error expected line number statement end statement

    I am getting this error and where th If not starts its is in red showing that is where the issue is


    HTML Code: 
    Sub RemoveRows()
    Dim LR As Long, i As Long
    Dim ws As Worksheet
    Set ws = Worksheets("100 Airports")
    LR = Range("B" & Rows.Count).End(xlUp).Row
    For i = LR To 10 Step -1
    
    If Not(Range("B" & i).Value = Ws.Range("E4") or (Range("B" & i).Value = Ws.Range("E5") or (Range("B" & i).Value = Ws.Range("E6") or (Range("B" & i).Value = Ws.Range("E7") or (Range("B" & i).Value = Ws.Range("E8") or (Range("B" & i).Value = Ws.Range("E9") or (Range("B" & i).Value = Ws.Range("E10") or (Range("B" & i).Value = Ws.Range("E11") or (Range("B" & i).Value = Ws.Range("E12") or (Range("B" & i).Value = Ws.Range("E13") or (Range("B" & i).Value = Ws.Range("E14") or (Range("B" & i).Value = Ws.Range("E15") or (Range("B" & i).Value = Ws.Range("E16") or (Range("B" & i).Value = Ws.Range("E17") or (Range("B" & i).Value = Ws.Range("E18") or (Range("B" & i).Value = Ws.Range("E19") or (Range("B" & i).Value = Ws.Range("E20") or (Range("B" & i).Value = Ws.Range("E21") or (Range("B" & i).Value = Ws.Range("E22") or (Range("B" & i).Value = Ws.Range("E23") or (Range("B" & i).Value = Ws.Range("E24") or (Range("B" & i).Value = Ws.Range("E25") or (Range("B" & i).Value = Ws.Range("E26") or (Range("B" & i).Value = Ws.Range
    ("E27") or (Range("B" & i).Value = Ws.Range("E28") or (Range("B" & i).Value = Ws.Range("E29") or (Range("B" & i).Value = Ws.Range("E30") or (Range("B" & i).Value = Ws.Range("E31") or (Range("B" & i).Value = Ws.Range("E32") or (Range("B" & i).Value = Ws.Range("E33") or (Range("B" & i).Value = Ws.Range("E34") or (Range("B" & i).Value = Ws.Range("E35") or (Range("B" & i).Value = Ws.Range("E36") or (Range("B" & i).Value = Ws.Range("E37") or (Range("B" & i).Value = Ws.Range("E38") or (Range("B" & i).Value = Ws.Range("E39") or (Range("B" & i).Value = Ws.Range("E40") or (Range("B" & i).Value = Ws.Range("E41") or (Range("B" & i).Value = Ws.Range("E42") or (Range("B" & i).Value = Ws.Range("E43") or (Range("B" & i).Value = Ws.Range("E44") or (Range("B" & i).Value = Ws.Range("E45") or (Range("B" & i).Value = Ws.Range("E46") or (Range("B" & i).Value = Ws.Range("E47") or (Range("B" & i).Value = Ws.Range("E48") or (Range("B" & i).Value = Ws.Range("E49") or (Range("B" & i).Value = Ws.Range("E50") or (Range("B" & i).Value =
    Ws.Range("E51") or (Range("B" & i).Value = Ws.Range("E52") or (Range("B" & i).Value = Ws.Range("E53") or (Range("B" & i).Value = Ws.Range("E54") or (Range("B" & i).Value = Ws.Range("E55") or (Range("B" & i).Value = Ws.Range("E56") or (Range("B" & i).Value = Ws.Range("E57") or (Range("B" & i).Value = Ws.Range("E58") or (Range("B" & i).Value = Ws.Range("E59") or (Range("B" & i).Value = Ws.Range("E60") or (Range("B" & i).Value = Ws.Range("E61") or (Range("B" & i).Value = Ws.Range("E62") or (Range("B" & i).Value = Ws.Range("E63") or (Range("B" & i).Value = Ws.Range("E64") or (Range("B" & i).Value = Ws.Range("E65") or (Range("B" & i).Value = Ws.Range("E66") or (Range("B" & i).Value = Ws.Range("E67") or (Range("B" & i).Value = Ws.Range("E68") or (Range("B" & i).Value = Ws.Range("E69") or (Range("B" & i).Value = Ws.Range("E70") or (Range("B" & i).Value = Ws.Range("E71") or (Range("B" & i).Value = Ws.Range("E72") or (Range("B" & i).Value = Ws.Range("E73") or (Range("B" & i).Value = Ws.Range("E74") or (Range("B" & i)
    .Value = Ws.Range("E75") or (Range("B" & i).Value = Ws.Range("E76") or (Range("B" & i).Value = Ws.Range("E77") or (Range("B" & i).Value = Ws.Range("E78") or (Range("B" & i).Value = Ws.Range("E79") or (Range("B" & i).Value = Ws.Range("E80") or (Range("B" & i).Value = Ws.Range("E81") or (Range("B" & i).Value = Ws.Range("E82") or (Range("B" & i).Value = Ws.Range("E83") or (Range("B" & i).Value = Ws.Range("E84") or (Range("B" & i).Value = Ws.Range("E85") or (Range("B" & i).Value = Ws.Range("E86") or (Range("B" & i).Value = Ws.Range("E87") or (Range("B" & i).Value = Ws.Range("E88") or (Range("B" & i).Value = Ws.Range("E89") or (Range("B" & i).Value = Ws.Range("E90") or (Range("B" & i).Value = Ws.Range("E91") or (Range("B" & i).Value = Ws.Range("E92") or (Range("B" & i).Value = Ws.Range("E93") or (Range("B" & i).Value = Ws.Range("E94") or (Range("B" & i).Value = Ws.Range("E95") or (Range("B" & i).Value = Ws.Range("E96") or (Range("B" & i).Value = Ws.Range("E97") or (Range("B" & i).Value = Ws.Range("E98") or (Range
    ("B" & i).Value = Ws.Range("E99") or (Range("B" & i).Value = Ws.Range("E100") or (Range("B" & i).Value = Ws.Range("E101") or (Range("B" & i).Value = Ws.Range("E102") or (Range("B" & i).Value = Ws.Range("E103")) Then Rows(i).Delete
    Next i
    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,316

    Re: compile error expected line number statement end statement

    What rows are you trying to remove?

    If it's rows that match/don't match a list of values then I'm sure there are better ways to do this rather than the huge Or expression you are trying to use.

    For example you could use Application.Match to see if a value in a cell is in the list of values.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    08-07-2013
    Location
    mo
    MS-Off Ver
    Excel 2010
    Posts
    453

    Re: compile error expected line number statement end statement

    The current range I am working is is about 85,000 rows long. I need to delete any rows that down contain a certain value in column "B". The list of values to look for in B is 100 values and they are in a worksheet called "100 Airports" in range E4:E103.

    I am trying to find the best way to do this because I have other sheets that are huge that contain formulas that search that huge range and it is going so slow. If I can cut the 85,000 rows with the data the formulas is searching for it will hopeffuly speed things up.

    What are your thoughs? Thanks for the help

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,316
    Like I suggested use Application.Match to see if the value in the cell from column B is in the list in column E on the other sheet.

    If it is delete the row.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Compile Error : line number or label or statement or end of statement
    By excellearner121 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2013, 06:41 PM
  2. [SOLVED] Compile Error: Expected: End of Statement
    By eemiller1997 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-14-2012, 09:37 AM
  3. [SOLVED] Compile error: Expected end of statement
    By nenadmail in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-29-2012, 07:12 PM
  4. Replies: 4
    Last Post: 05-16-2012, 05:33 PM
  5. How to fix a Compile Error Expected: End of statement.
    By AKarmyguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-03-2010, 02:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1