# is it possible to reference a named range in excel solver constraints?

#### breezer123

##### New Member
Hi all! Thank you for taking the time to read my question and hopefully help me with an answer! In this line "SolverAdd CellRef:="minrange", Relation:=3, FormulaText:="\$J\$2"" I am trying to constrain the cells in the "minrange" to be greater than J2. However, the current range of values in my spreadsheet has "" cells in the range. I tried to create minrange to have it be all of the non "" cells, or the cells with values in them. When I originally tried to put AN45:AN76 (the full range) in the spot where "minrange" currently is, my solver could not find a feasible solution because blank cells will never be greater than my J2 value. The minrange code could be incorrect, or this tactic may not be possible in solver. I am not sure if anyone knows the answer to this, or if they know how to help me with this solution. Thanks in advance!

VBA Code:
``````Dim minrange As Range, c As Range, i As Long
For Each c In Range("AN45:AN76")
If c.Value > 0 Then
Exit For
End If
Next
Set minrange = c
For i = minrange.Row + 1 To 76
If Range("AN" & i).Value > 0 Then
Set minrange = Union(minrange, Range("AN" & i))
End If
Next

SolverOk SetCell:="\$AN\$79", MaxMinVal:=1, ValueOf:=0, ByChange:="\$J\$9:\$J\$39", _
Engine:=1, EngineDesc:="GRG Nonlinear"

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### i_nth

##### New Member
You can use range names in Solver - actually, it is preferred as the models are easier to read.
However, the range must be contiguous. You seem to be creating a range that consists of multiple regions - that won't work.
You'll need to collate the cells you want to constrain into a contiguous block, then you can make the constraint.

#### breezer123

##### New Member
You can use range names in Solver - actually, it is preferred as the models are easier to read.
However, the range must be contiguous. You seem to be creating a range that consists of multiple regions - that won't work.
You'll need to collate the cells you want to constrain into a contiguous block, then you can make the constraint.
Thank you so much for clarifying! Do you know an easy way for me to make a new column with just the cells with values in them from my previous column? The number of cells with values can't be predefined though, so it would just need to keep filling until there is no more left. Then that would be the cell reference I would have, but even then it wouldnt be a specific range for solver because the number of rows would change. Sorry if that was confusing, do you understand my problem? Thanks for your help!

#### i_nth

##### New Member
Can you sort the cells in column AN so the ones you want are at the top? If so, you could then define a range that covers only the non-blank cells and use that in the Solver constraint.

Replies
13
Views
359
Replies
0
Views
240
Replies
1
Views
583
Replies
0
Views
285
Replies
1
Views
149

1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back