Too Many Line continuations?? VB Macro

[mkingsley]

I have a 10,000 line worksheet that I have constructed a macro for an
it works great. I had another request to create another one lookin
for different data. My original had this as a query component (lookin
for sales reps):

Dim i As Long
Dim iStart As Long
Dim iEnd As Long

iStart = 1
iEnd = Range("B65536").End(xlUp).Row

For i = iStart To iEnd
If Range("B" & i).Value <> "Claude Mossian" And _
Range("B" & i).Value <> "Craig Kowal" And _
Range("B" & i).Value <> "Dave Wester" And _
Range("B" & i).Value <> "David Stone" And _
Range("B" & i).Value <> "Elaine Ober" And _
Range("B" & i).Value <> "House Account INDiana" And _
Range("B" & i).Value <> "House Account Indiana Lab" And _
Range("B" & i).Value <> "Jill Campbell" And _
Range("B" & i).Value <> "Lana Compo" And _
Range("B" & i).Value <> "Steve Horvath" Then
Range("B" & i).Value = ""
End If
Next

the new data criteria are numbers, so I modified the old macro an
entered it like this:

Dim i As Long
Dim iStart As Long
Dim iEnd As Long

iStart = 1
iEnd = Range("C65536").End(xlUp).Row

For i = iStart To iEnd
If Range("C" & i).Value <> "21012" And _
Range("C" & i).Value <> "21056" And _
Range("C" & i).Value <> "22053" And _
Range("C" & i).Value <> "23801" And _
Range("C" & i).Value <> "24028" And _
Range("C" & i).Value <> "24087" And _
Range("C" & i).Value <> "24091" And _
Range("C" & i).Value <> "24095" And _
Range("C" & i).Value <> "24114" And _
Range("C" & i).Value <> "24189" And _
Range("C" & i).Value <> "25233" And _
Range("C" & i).Value <> "25316" And _
Range("C" & i).Value <> "30402" And _
Range("C" & i).Value <> "34209" And _
Range("C" & i).Value <> "38417" And _
Range("C" & i).Value <> "38820" And _
Range("C" & i).Value <> "39206" And _
Range("C" & i).Value <> "39802" And _
Range("C" & i).Value <> "39803" And _
Range("C" & i).Value <> "39856" And _
Range("C" & i).Value <> "42022" And _
Range("C" & i).Value <> "45894" And _
Range("C" & i).Value <> "69428" And _
Range("C" & i).Value <> "70393" And _
Range("C" & i).Value <> "331961" Then
Range("C" & i).Value = ""
End If
Next

I have 40 customer numbers to query, but now every time I try to ad
another line, I get "too many line continuations"

Any help is appreciated.

Mike Kingsle

 

[JE McGimpsey]

I would store the account numbers in another worksheet (perhaps hidden),
then use

Dim rCell As Range
Dim rCriteria As Range
Application.ScreenUpdating = False
With Sheets("Hidden Criteria Sheet")
Set rCriteria = .Range("A1:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
End With
For Each rCell In Range("C1:C" & _
Range("C" & Rows.Count).End(xlUp).Row)
If Not Application.CountIf(rCriteria, rCell.Value) Then _
rCell.ClearContents
Next rCell
Application.ScreenUpdating = True

This allows you to add or delete criteria without ever having to change
the macro.

 

[Bernie Deitrick]

Mike,

Simply break up your check into nested IF statements:

For i = istart To iend
If Range("C" & i).Value <> "21012" And _
Range("C" & i).Value <> "21056" And _
Range("C" & i).Value <> "22053" And _
Range("C" & i).Value <> "23801" And _
Range("C" & i).Value <> "24028" And _
Range("C" & i).Value <> "24087" And _
Range("C" & i).Value <> "24091" And _
Range("C" & i).Value <> "24095" And _
Range("C" & i).Value <> "24114" And _
Range("C" & i).Value <> "24189" And _
Range("C" & i).Value <> "25233" And _
Range("C" & i).Value <> "25316" And _
Range("C" & i).Value <> "30402" Then
If Range("C" & i).Value <> "34209" And _
Range("C" & i).Value <> "38417" And _
Range("C" & i).Value <> "38820" And _
Range("C" & i).Value <> "39206" And _
Range("C" & i).Value <> "39802" And _
Range("C" & i).Value <> "39803" And _
Range("C" & i).Value <> "39856" And _
Range("C" & i).Value <> "42022" And _
Range("C" & i).Value <> "45894" And _
Range("C" & i).Value <> "69428" And _
Range("C" & i).Value <> "70393" And _
Range("C" & i).Value <> "331961" Then
Range("C" & i).Value = ""
End If
End If
Next

OR - my preference for ease of maintenance:

Sub BetterCheck()
Dim myArray As Variant
Dim i As Long
Dim iStart As Long
Dim iEnd As Long

iStart = 1
iEnd = Range("C65536").End(xlUp).Row

myArray = Array("21012", "21056", "22053", "23801", "24028", _
"24087", "24091", "24095", "24114", "24189", _
"25233", "25316", "30402", "34209", "38417", _
"38820", "39206", "39802", "39803", "39856", _
"42022", "45894", "69428", "70393", "331961")

For i = istart To iend
If IsError(Application.Match(CStr(Range("C" & i).Value), _
myArray, False)) Then
Range("C" & i).Value = ""
End If
Next i
End Sub

HTH,
Bernie
MS Excel MVP

 

[Toppers]

Hi,
Perhaps hold the (exlusion) comparison data in a list- use named
Range ? - and use MATCH function to check if the entry is to be excluded
i.e.found in MATCH. If not , set your field to "", else move to next row.


OR add more than one item per line

If Range("C" & i).Value <> "21012" AND Range("C" & i).Value <> "21056" And _
Range("C" & i).Value <> "22053" And Range("C" & i).Value <> "23801" And _
Range("C" & i).Value <> "24028" And _


HTH

 

[George Nicholson]

Yet another approach:

For i = iStart To iEnd
Select Case Range("C" & i).Value
Case "21012", "21056","22053", "23801", "24028"
'Do Nothing
Case "24087", "24091", "24095","24114", "24189"
'Do Nothing
Case "25233" , "25316", "30402", "34209", "38417"
'Do Nothing
Case "38820" , "39206", "38902", "39803", "39856"
'Do Nothing
Case "42022" , "45894", "69428", "70393", "331961"
'Do Nothing
Case Else
' Value doesn't equal anything in our list
Range("C" & i).Value = ""
End Select
Next i

If you run into a problem because you are comparing numbers to strings
(depending on your data), you can either 1) remove the quotation marks
around the Case values or 2) use - "Select Case Cstr(Range("C" &
).Value)" - instead.

HTH,

 

[Nick Hebb]

A really simple way:

Dim sCriteria As String
sCriteria = "21056 22053 23801 24028 24087 24091 24095 24114 " _
& "24189 25233 25316 30402 34209 38417 38820 39206 39802 " _
& "39803 39856 42022 45894 69428 70393 331961"

If InStr(sCriteria, Range("B" & i).Value) >= 1 Then
Range("B" & i).ClearContents
End If

The only caveat is if you have one value that is a string subset of
another - e.g. 2505 and 25056, then the cell with 2505 would also be
cleared. To resolve this, you could delimit the string values above
with ";", making sure you have a trailing ; on the last value. The
Instr(0 would change to:
If InStr(sCriteria, Range("B" & i).Value & ";") >= 1 Then

 

[Nick Hebb]

Typo corrections:

21056 22053 23801 24028 24087 24091 24095 24114 " _
& "24189 25233 25316 30402 34209 38417 38820 39206 39802 " _
& "39803 39856 42022 45894 69428 70393 331961

"

Should read:

21056 22053 23801 24028 24087 24091 24095 24114 " _
& "24189 25233 25316 30402 34209 38417 38820 39206 39802 " _
& "39803 39856 42022 45894 69428 70393 331961"

And, Instr(0 should be Instr() .

 

[Dana DeLouis]

For tracking many names, I prefer something along this line. Make a Table
of your data, and give it a range name (ie "Tbl". This should make it easy
to keep updated.
Then, use Data Validation. Adjust for your own range.

Sub Demo()
Dim rng As Range

With [A1:A20].Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="=Tbl"

For Each rng In .Parent.Cells
If rng.Validation.Value Then rng.ClearContents
Next

.Delete ' Delete Validation
End With
End Sub

HTH :>)

 

[Nick Hebb]

If the criteria list may change over time, I would definitely use one
of the other suggestions such as Dana's. I just threw my suggestion
out there because there are a lot of times that solutions get
over-engineered when a quick and dirty (and succinct) solution will
suffice.