Simplify this into one sentence only

Question

How can I simplify this into one sentence in vba?

 If [BT12] = "a" Then
   Range("AB12").ClearContents
 End If

 If [BT13] = "a" Then
   Range("AB13").ClearContents
 End If

 If [BT14] = "a" Then
   Range("AB14").ClearContents
 End If

you could take this code to a Sub and have your _main_ code with a single line calling the Sub — DisplayName, Sep 01 '18 at 16:47

Scott Craner · Accepted Answer · 2018-09-01T20:43:34.947

4

Here it is in one line:

[AB12:AB14] = [IF(BT12:BT14 = "a","",AB12:AB14)]

Here is another using UNION:

Union(IIf([BT12] = "a", [AB12], [AFD1040000]), IIf([BT13] = "a", [AB13], [AFD1040000]), IIf([BT14] = "a", [AB14], [AFD1040000])).ClearContents

The first creates an array of the values, either "" or the value in the cell. It is very concise and can easily be expanded to include a larger range. The drawback is that if the data in AB is filled by formula the formula will be replaced by the value and the formula will be removed.

The second only clears those that need to be cleared leaving the others unchanged but is not as easily editable with larger ranges.

edited Sep 01 '18 at 20:43

answered Sep 01 '18 at 20:22

Scott Craner

148,073
10
49
81

this third version of an one liner is brilliant +1) – T.M. Sep 01 '18 at 20:33
1

Fine solutions, indeed – DisplayName Sep 01 '18 at 21:09

DisplayName · Answer 2 · 2018-09-01T18:33:56.447

2

here's your one-sentence code:

If Not [BT12:BT14].Find("a", , xlValues, xlWhole) Is Nothing Then Range(IIf([BT12] = "a", IIf([BT13] = "a", IIf([BT14] = "a", "AB12:AB14", "AB12:AB13"), IIf([BT14] = "a", "AB12,AB14", "AB12")), IIf([BT13] = "a", IIf([BT14] = "a", "AB13:AB14", "AB13"), IIf([BT14] = "a", "AB14", "")))).ClearContents

that you can (possibly) read more comfortably as:

    If Not [BT12:BT14].Find("a", , xlValues, xlWhole) Is Nothing Then Range( _
           IIf([BT12] = "a", _
                             IIf([BT13] = "a", _
                                               IIf([BT14] = "a", "AB12:AB14", "AB12:AB13"), _
                                               IIf([BT14] = "a", "AB12,AB14", "AB12") _
                                 ), _
                             IIf([BT13] = "a", _
                                               IIf([BT14] = "a", "AB13:AB14", "AB13"), _
                                               IIf([BT14] = "a", "AB14", "") _
                                 ) _
              ) _
          ).ClearContents

edited Sep 01 '18 at 18:33

answered Sep 01 '18 at 16:28

DisplayName

13,283
2
11
19

3

Don't bother much because I'm almost sure the OP will turn out to be asking for something completely different (my guess he has many `BTXX`s and `ABXX`s and he doesn't want to repeat the code for all of them). Some users just ask an _unclear_ question and don't even bother to be there to respond. Don't put so much effort into that :) – 41686d6564 stands w. Palestine Sep 01 '18 at 17:31
1

@AhmedAbdelhameed, it's been just a funny exercise – DisplayName Sep 01 '18 at 17:34
That's awesome. – Parrish Husband Sep 01 '18 at 18:00
@DisplayName, nice alternative +1; see my approach using the `Application.Index` function. – T.M. Sep 01 '18 at 19:25
Using union on can simplify the IIF's. See below. But the logic is sound here. – Scott Craner Sep 01 '18 at 20:49

score 2 · Answer 3 · answered Sep 01 '18 at 17:33

2

This is bad practice and not recommended but is a one-liner. It is expected that this would be wrapped in a With statement holding the parent sheet reference.

Dim  i As Long: For i = 12 To 14: If .Cells(i, 72).Value = Chr$(97) Then .Cells(i, 28).ClearContents: Next

answered Sep 01 '18 at 17:33

QHarr

83,427
12
54
101

We don't know if OP is getting variable values from cells or not. As I understood it, `[BTXX]' is just a variable right? – Parrish Husband Sep 01 '18 at 18:03
1

@ParrishHusband No. It is shorthand notation for range reference. It uses the Evaluate method. https://stackoverflow.com/questions/44601486/compare-different-ranges-with-bracket-notation – QHarr Sep 01 '18 at 18:04
Interesting, have a link for that I can read? I feel like I'm one of XKCD's 10,000 today. – Parrish Husband Sep 01 '18 at 18:05
1

And the official blurb https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-cells-by-using-shortcut-notation – QHarr Sep 01 '18 at 18:07
2

Strictly speaking, this is not a one sentence code line. Whatever this should be useful for... – DisplayName Sep 01 '18 at 18:33

T.M. · Answer 4 · 2018-09-01T19:50:48.230

2

Yes, it can be done :-)

Just for fun: assuming you provide an ► empty cell in [AB11], you can use this one liner via the Application.Index function:

[AB11:AB14] = Application.Transpose(Application.Index([AB11:AB14], Array(1, IIf([BT12] = "a", 1, 2), IIf([BT13] = "a", 1, 3), IIf([BT14] = "a", 1, 4)), 1))

(Edit thx DisplayName:)

Amplifying remarks to the Index function

You can find amplifying remarks to the use of the Index function at Insert first column in array without Loops or API calls

edited Sep 01 '18 at 19:50

answered Sep 01 '18 at 19:17

T.M.

9,436
3
33
57

1

I can't see how to reach the same functionality OP required. Did you test it? – DisplayName Sep 01 '18 at 19:37
@DisplayName - thank you, corrected answer, as I confused the columns. – T.M. Sep 01 '18 at 19:52
1

No need to use the `application`. Just use `Evaluate()` then you can use IF in an array. See my answer. But this gets closer to what the OP wanted as a true single line. – Scott Craner Sep 01 '18 at 20:29

GMalc · Answer 5 · 2018-09-02T01:53:11.667

1

For what you are asking for; the IIF function works, whether you are evaluating using a True or False clause. Try this one liner.

For Each cel In Range("BT12:BT14"): IIf cel = "a", cel.Offset(, -44).ClearContents, True: Next

edited Sep 02 '18 at 01:53

answered Sep 01 '18 at 18:39

GMalc

2,608
1
9
16

score 0 · Answer 6 · answered Sep 01 '18 at 16:57

How can I simplify this into one sentence in vba?

Should you? Your example code is concise, and easy to read. As Rawrplus points out, shorter for the sake of being shorter is usually not a good idea.

One could simplify each line to this:

If [BT12] = "a" Then Range("AB12").ClearContents
If [BT13] = "a" Then Range("AB13").ClearContents
If [BT14] = "a" Then Range("AB14").ClearContents

Is that better? Arguably no in my opinion. If the actions of the condition ever need more complexity, you'll be forced to refactor anyway.

_"Is that better? Arguably no in my opinion."_. Well I do think that your code is better then the OP's one — DisplayName, Sep 01 '18 at 17:25

C. van Dorsten · Answer 7 · 2018-09-02T18:45:21.013

-1

The quick answer is: it cant' be done. But one could figure out a solution if one want's to although it be rather far fetched.

First create a sub to put in a library module.

Sub ClearCellContent(ParamArray Args())
  Dim i As Integer, J As Integer
  i = UBound(Args)
  For J = 0 To i Step 3
    If Args(J).value = Args(J + 1) Then Args(J + 2).ClearContents
  Next
End Sub

From a somewhere in your code you can now call this sub like so:

ClearCellContent [BT12], "a", [AB12], [BT13], "a", [AB13], [BT14], "a", [AB14]

Like I said, far fetched but a oneliner of sorts.

edited Sep 02 '18 at 18:45

answered Sep 01 '18 at 18:16

C. van Dorsten

152
7

1

_”The quick answer is: it cant' be done”_: well, that’s not true as you can see in my answer – DisplayName Sep 01 '18 at 18:35

Simplify this into one sentence only

7 Answers7