Excel Macro Too Many Line Continuations

#1

yuenk is offline

Forum Contributor


Too many line continuations

In a column 3 I tend to keep on accumulating values that need to be removed. I now have too many values in that column now that I am getting an error of "Too many line continuations." How do I resolve this?

For i = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
If Cells(i, 4).Value = "INIT" Or Cells(i, 4).Value = "DES" Or _
Cells(i, 3).Value = 1908425 Or _
Cells(i, 3).Value = 1632492 Or _
Cells(i, 3).Value = 2588948 Or _
Cells(i, 3).Value = 2644451 Or _
Cells(i, 3).Value = 2654319 Or _
Cells(i, 3).Value = 1794637 Or _
Cells(i, 3).Value = 1722139 Or _
Cells(i, 3).Value = 2655788 Or _
Cells(i, 3).Value = 1796065 Or _
Cells(i, 3).Value = 1489608 Or _
Cells(i, 3).Value = 1794637 Or _
Cells(i, 3).Value = 1770914 Or _
Cells(i, 3).Value = 1495217 Or _
Cells(i, 3).Value = 1492028 Or _
Cells(i, 3).Value = 1983020 Or _
Cells(i, 3).Value = 2122683 Or _
Cells(i, 3).Value = 1507671 Or _
Cells(i, 3).Value = 1593761 Or _
Cells(i, 3).Value = 2458957 Or _
Cells(i, 3).Value = 1782458 Or _
Cells(i, 3).Value = 2240527 Or _
Cells(i, 3).Value = 2319018 Or _
Cells(i, 3).Value = 2084908 Or _
Cells(i, 3).Value = 2029397 Or _
Cells(i, 3).Value = 1896445 Or _
Cells(i, 3).Value = 1908373 And Cells(i, 22) = 11400 Or _
Cells(i, 5).Value = "1234567" Or Cells(i, 5).Value = "1234567" Then _
Rows(i).Delete
Next
For Each cell In ActiveSheet.UsedRange
cell.Value = Trim(cell)
Next cell

Thanks for the help.


#2

Re: Too many line continuations

I'd just put more on a single line. Or you can use a Select Case, but with multiple cells to check values of, collapsing the If would be cleaner:

Want to show appreciation for the help you received from a member? Give them reps by clicking the bottom left of their post!


#3

Re: Too many line continuations


#4

Re: Too many line continuations

Interesting that you've thrown an And in there: and that you've duplicated the: I'd be inclined to bracket the And as I'm not entirely sure how it will work out in the midst of all those Ors. If it were me, I'd put all those codes in a "Configuration" sheet, which you can hide, and use Countif to check if the code is present in the list. That makes it easier to make changes to the list without having to adapt your code ... with maybe the exception of the And.

Regards, TMS


#5

Re: Too many line continuations

I think it might be worth it to introduce a boolean variable

#6

Re: Too many line continuations

Hi,

If it were me I'd approach this from a different angle. I don't like using time consuming loops when there is much more efficient functionality available.
In this case I'd create a list of all the values that need deleting and name it 'mylist'.

Then in a helper column alongside your data add a MATCH formula that determines whether to keep the row or not. i.e.

The helper column formula should be adapted to include the special conditions for the different And/Or combination you show as the last items but this should be relatively trivial.

Assuming the helper column is column Z
Then your macro can be a simple autofilter

Last edited by Richard Buttrey; 11-09-2015 at 11:01 AM. Reason: End WIth missing

#7

Re: Too many line continuations

Agree with the approach. Formula could be a little shorter: or even

Regards, TMS


#8

Re: Too many line continuations

I do this all the time in practice. Much faster. Also, I'll typically sort the data on the Keep/Delete key, as deleting non-continuous ranges takes additional time, and if there are enough individual ranges, can tank Excel completely.

agostoonancery.blogspot.com

Source: https://www.excelforum.com/excel-programming-vba-macros/1112625-too-many-line-continuations.html

0 Response to "Excel Macro Too Many Line Continuations"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel