3 replies to this topic

[fragilespark]

I'm editing an Excel 2003 Macro created by someone else, and learning along the way. There is data from another report that is being imported and we want to split it into different sheets, to correspond to sales areas, depending on account number. This is done by deleting unnecessary rows. For example

Account, name, ordernumber
410027, ABC, 123
420001, XYZ, 789

where the first account number is in one sales area and the second is in another. It is not possible to add it to the original report as that imports from the system and doesn't have this data.

Some areas are easy because the accounts are in a specific range, which works like this:

If Account < 420000 Or Account > 489999 Then

(then delete selected rows etc) which returns info on account 420001 onwards. Perfect. However the area I'm working on also has a few isolated accounts which I have managed to bring up with the following:

If Account <> 400000 And Account <> 400001 And Account <> 400003 And Account <> 400005 And Account <> 400015 Then

Which deletes everything but those accounts. My question is, how do I combine the two so it brings up anything in the range plus the individual accounts? I have tried both

If Account < 420000 Or Account > 489999 And Account <> 400000 And Account <> 400001 And Account <> 400003 And Account <> 400005 And Account <> 400015 And Account Then

and

If Account < 420000 Or Account > 489999 Or Account <> 400000 And Account <> 400001 And Account <> 400003 And Account <> 400005 And Account <> 400015 And Account Then

but as it deletes everything I am wondering if this is not how it is done and hoping someone can point me in the right direction please. Thank you!

(If I can't make it work like this then I will have to propose instead of deleting rows we can inverse it and select the ones we want, but that will mean changing a lot more code)

[WingedPanther]

You should add parenthesis to group the OR's and AND's to get the effect you want. Otherwise, you can get that type of unexpected results. In this case, wrap the block of AND statements in parenthesis.

[fragilespark]

Many thanks for your quick answer. I have tried the following:

If Account < 420000 Or Account > 489999 And (Account <> 400000 And Account <> 400001 And Account <> 400003 And Account <> 400005 And Account <> 400009 And Account <> 410000 And Account <> 410005 And Account <> 410007 And Account <> 410019 And Account <> 410028) Then
     Rows(iRowData).Select
     Selection.Delete Shift:=xlUp
     End If

However, it still brings up only account 420001 and not, for example 400005, which I can see information for on the original sheet. Have I put the parenthesis in the wrong place? Thank you

EDIT: I spoke too soon! Added Parentheses to the range as well and it worked.

[WingedPanther]

I'm glad to hear it