Merge code

[Russk68]

Hi
Is it possible to put the code into 1 line like my first line is? When I tried this, only the first cell filled in.


So if any one of these cells is blank, "Spare" will fill in.

 If Range("C5,E5,G5,I5,K5,M5") = "" Then Range("C5,E5,G5,I5,K5,M5") = "Spare"

 If Range("C5") = "" Then Range("C5") = "Spare"
    If Range("E5") = "" Then Range("E5") = "Spare"
    If Range("G5") = "" Then Range("G5") = "Spare"
    If Range("I5") = "" Then Range("I5") = "Spare"
     If Range("K5") = "" Then Range("K5") = "Spare"
    If Range("M5") = "" Then Range("M5") = "Spare"
/CODE]

TIA

 

[rlv01]

You can't do it that way, but there are other ways to do what you want.

    Dim R As Range, S As String

    For Each R In Range("C5,E5,G5,I5,K5,M5")
        S = S & Trim(R.Text)
    Next R
    If S = "" Then
        Range("C5,E5,G5,I5,K5,M5") = "Spare"
    End If

 

[JoeMo]

If the intent is just to shorten the code while filling any blanks in the non-contiguous range with the string "Spare", maybe:

Sub Test()
Dim Rng As Range, c as Range
Set Rng = Range("C5,E5,G5,I5,K5,M5")
If Application.CountA(Rng) < Rng.Count Then
    For Each c In Rng
        If c.Value = "" Then c.Value = "Spare"
    Next c
End If
End Sub

 

[Fluff]

Another option

   On Error Resume Next
   Range("C5,E5,G5,I5,K5,M5").SpecialCells(xlBlanks).Value = "Spare"
   On Error GoTo 0

 

[Russk68]

Thank you all!
If I put in 3000 cell addresses, will it slow my sheet down?

 

[Nick Olander]

You can't do it that way, but there are other ways to do what you want.

    Dim R As Range, S As String

    For Each R In Range("C5,E5,G5,I5,K5,M5")
        S = S & Trim(R.Text)
    Next R
    If S = "" Then
        Range("C5,E5,G5,I5,K5,M5") = "Spare"
    End If

How do you merge code?

 

[rlv01]

Thank you all!
If I put in 3000 cell addresses, will it slow my sheet down?

Of course. But the question you should be asking is will it slow down enough to matter? The way to answer that question is to conduct a test.

 

[RoryA]

If you put in 3000 addresses, it won't work, since the Range property only accepts a 255 character address string. You'll have to use Union. Although it looks like you are simply doing every other column, so you could use a simple loop with step 2 over the whole range.

 

[Russk68]

If you put in 3000 addresses, it won't work, since the Range property only accepts a 255 character address string. You'll have to use Union. Although it looks like you are simply doing every other column, so you could use a simple loop with step 2 over the whole range.

Got it! Thank you!