clean up code (if possible)....

[jproffer]

If ActiveCell.Address = Range("A31").Address And ActiveCell.Value = "" Then
        UserForm1.ComboBox1.RowSource = "Lists!A1:A23"
        UserForm1.Show
    End If
    If ActiveCell.Address = Range("A32").Address And ActiveCell.Value = "" Then
        UserForm1.ComboBox1.RowSource = "Lists!A1:A23"
        UserForm1.Show
    End If
    If ActiveCell.Address = Range("A33").Address And ActiveCell.Value = "" Then
        UserForm1.ComboBox1.RowSource = "Lists!A1:A23"
        UserForm1.Show
    End If
    If ActiveCell.Address = Range("A34").Address And ActiveCell.Value = "" Then
        UserForm1.ComboBox1.RowSource = "Lists!A1:A23"
        UserForm1.Show
    End If
    If ActiveCell.Address = Range("A35").Address And ActiveCell.Value = "" Then
        UserForm1.ComboBox1.RowSource = "Lists!A1:A23"
        UserForm1.Show
    End If
    If ActiveCell.Address = Range("A36").Address And ActiveCell.Value = "" Then
        UserForm1.ComboBox1.RowSource = "Lists!A1:A23"
        UserForm1.Show
    End If

Is it possible to combine all these separate cells into an array or range of cells?

I've tried:

If ActiveCell.Address = Range("A31:A36").Address And ActiveCell.Value = "" Then

But no luck there.

Also, while we're at it. In this example, they are a continuous range (A31 thru A36). Is there a way to add more continuous but separate ranges to the same If statement?

For instance:

If ActiveCell.Address = Range("A31:A36, D4:D9, J56:J68").Address And ActiveCell.Value = "" Then

I'm (fairly) sure there is a much cleaner way to accomplish what's in the top code box here, but I just can't find the right syntax.

Thank you very much, in advance, for any help you can provide.

 

[Fluff]

For the first part, how about

   If Not Intersect(ActiveCell, Range("A31:A36")) Is Nothing Then
      UserForm1.ComboBox1.RowSource = "Lists!A1:A23"
      UserForm1.Show
    End If

 

[mse330]

I think you can accomplish that using intersect ... Just check the below link for more clarification

http://www.ozgrid.com/VBA/vba-intersect.htm

 

[jproffer]

That just goes to show what a noob I am lol. That did it. Thank you to both of you for the coding and link. Worked perfectly.

And just for the sake of future searches, etc. As to the second part of my question, it is possible to string together several ranges into the same If statement. For instance:

If Not Intersect(ActiveCell, Range("A31:A36, D4:D8, J57:J75")) Is Nothing Then

 

[Fluff]

Glad we could help & thanks for the feedback