-1
\$\begingroup\$

How can i improve the following working code?

I would like to be able to join the "Select Case True" with the normal "Select Case" conditions

With w.Sheets("test")
    For i = n To 10 Step -1
        Select Case .Cells(i, 1)
            Case 10111,23232,98076,41324
                .Cells(i, 1).EntireRow.Delete
        End Select
        Select Case True
            Case .Cells(i, 1) Like "A*"
                .Cells(i, 1).EntireRow.Delete
        End Select
    Next i
End With

Thank You

\$\endgroup\$
  • \$\begingroup\$ With your last edit, you invalidated the current answer. Something we don't take lightly on Code Review. However, reverting the question back to it's original state would make it off-topic, since example code is off-topic. For among others exactly the reason you just ran into, the answer was less helpful to you because you didn't provide the code you actually use. Please look at the help center and make sure the current code is actual code. \$\endgroup\$ – Mast Dec 6 '17 at 13:55
  • \$\begingroup\$ do you not consider confidential data? a lot of people cannot post actual code for this reason so i have to change it slightly \$\endgroup\$ – user1 Dec 6 '17 at 13:56
  • 1
    \$\begingroup\$ Confidential data shouldn't be posted. You can make modifications shifting the data a bit if that has no implications for the code involved, but you can't simplify it like you did. \$\endgroup\$ – Mast Dec 6 '17 at 13:59
  • \$\begingroup\$ then please advise me as to which community this belongs in. i cant post this on overflow - that's for assisting others with code problems. \$\endgroup\$ – user1 Dec 6 '17 at 14:03
  • 1
    \$\begingroup\$ @user1 How is "10111,23232,98076,41324" possibly confidential? Your question does belong here, but please avoid simplifying your code in the future. \$\endgroup\$ – Simon Forsberg Dec 6 '17 at 16:03
4
\$\begingroup\$

It depends. If the list is reasonably finite (two or three elements), then you could make it a bit spaghetti and say

        Select Case True
            Case .Cells(i, 1) Like "A*"
                .Cells(i, 1).EntireRow.Delete
            Case .Cells(i, 1) = 10111
                .Cells(i, 1).EntireRow.Delete
            Case .Cells(i, 1) = 23232
                .Cells(i, 1).EntireRow.Delete
            ...
        End Select

But this has the terrible disadvantage of getting nasty very quickly. Other options:

With w.Sheets("test")
    For i = n To 10 Step -1
        Select Case .Cells(i, 1)
            Case 10111,23232,98076,41324
                .Cells(i, 1).EntireRow.Delete
            Case Else
                If .Cells(i, 1) Like "A*" Then
                    .Cells(i, 1).EntireRow.Delete
                End If
        End Select
    Next i
End With

By the way, calling your workbook just plain w is quite a poor variable naming, choose something more explicit and informative like myImportantWorkbookOnThisDay or whatever. I like the camelCase, but you can choose any casing you like. Just some revealing name.

\$\endgroup\$
  • \$\begingroup\$ Thanks for the solution. i will accept. Whilst it works, i dont think mixing Switches and If statements makes for easily understandable code. I think having the seperate TRUE condition allows us to accept that select cannot accept 2 different data types in one condition, so we must seperate them. Appreciate the input! and will be more mindful of how i post code in the future :) \$\endgroup\$ – user1 Dec 7 '17 at 8:35
  • \$\begingroup\$ @user1, unfortunately VBA has a bit stiff Select Case statement. You can evaluate only one condition type. You give a value and the Select Case will run the Case that matches this value, both on its type and its evaluation. If you give it a Boolean (True), it will run the Case that evaluates to this boolean. If you give it a String, it will run the Case that evaluates to a String of the same contents. Give it a Long, and it will run the Case that evaluates to a Long of the same value... You get the idea :) \$\endgroup\$ – Nelson Vides Dec 7 '17 at 9:41
  • \$\begingroup\$ yep indeed! i wonder if there is a function that can handle multiple data types \$\endgroup\$ – user1 Dec 7 '17 at 10:31

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.