how to set a range in vba to be the the nonzero values in a column (skipping the zero cells)

breezer123

New Member
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Hi all! Thank you for taking the time to read my question and potentially offer some help!

I am trying to use solver for an optimization problem. One of my constraints is that the cells in this range must be greater than or equal to an input cell value. However, I want the range to not include the cells that are zero, as this makes the constraint non feasible in solver. My range AN45:AN76 is changing as the solver is running. However, some of the values in the range are zero (or I can easily make them "" if this helps with the solution). The values that are zero are not always the same ones in the column, so I need to find a way to call the range to be all the cells except for the zero ones. My attempt is in this line: minrange = Range(isnot(IsEmpty("$AN$45:$AN$76"))). Thank you in advance for any advice!

VBA Code:
Sub solvermacro()

Application.ScreenUpdating = False
Application.ThisWorkbook.Sheets("Calculations").Activate

Dim minrange As Range
minrange = Range(isnot(IsEmpty("$AN$45:$AN$76")))


SolverOk SetCell:="$AN$79", MaxMinVal:=1, ValueOf:=0, ByChange:="$J$9:$J$39", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$AN$45:$AN$76", Relation:=3, FormulaText:="$J$2"
SolverAdd CellRef:="$J$9:$J$39", Relation:=1, FormulaText:="1.25"
SolverAdd CellRef:="$J$9:$J$39", Relation:=3, FormulaText:="0.75"


SolverOptions Iterations:=10, Precision:=0.1, Convergence:= _
        0.001, StepThru:=False, Scaling:=False, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=0, RandomSeed:=0, MutationRate:=0.075, Multistart:= _
    False, RequireBounds:=False, MaxSubproblems:=0, MaxIntegerSols:=0, IntTolerance _
    :=1, SolveWithout:=False, MaxTimeNoImp:=30

solversolve
 
End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
assuming that AN45 will always be > 0 this would set minrange to only cells that have values > 0.

VBA Code:
Set minrange = Range("AN45")
For i = 46 To 76
    If Range("AN" & i).Value > 0 Then
        Set minrange = Union(minrange, Range("AN" & i))
    End If
Next
 

breezer123

New Member
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Thank you so much! This worked for me! Is there an easy tweak to make in the case where AN45 is 0?


assuming that AN45 will always be > 0 this would set minrange to only cells that have values > 0.

VBA Code:
Set minrange = Range("AN45")
For i = 46 To 76
    If Range("AN" & i).Value > 0 Then
        Set minrange = Union(minrange, Range("AN" & i))
    End If
Next
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
To use the Union method for developing your range, the variable 'mnrange has to be seeded with a valid range, and then you can continue to build the range within the variable by using the If Then statement. You can use AN45 as the seed range even if it is zero but it would then distort your Solver solution. I just threw that together quickly, without putting a lot of thought into it to otherwise eliminate the cells with non-positive values. If I can think of a better way, I will post it on this thread.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

This is a little lengthier but I believe it will allow you to have AN45 = 0 or "" and still get all your positive values in the range. If you have the variables declared elsewhere in the code, don't declare them again in the Dim statement or you will get an alert telling you you can't do that.

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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
This might also work.

VBA Code:
Range("AN44:AN76").AutoFilter 1, ">0"
Set minrange = Range("AN45:AN76").SpecialCells(xlCellTypeVisible)
ActiveSheet.AutoFilterMode = False
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,435
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Here's another possibility that establishes the variable R as a range within AN45:AN76 that excludes cells (including AN45) that have a value of zero.
VBA Code:
Sub IgnoreZeroCells()
Dim R As Range
On Error Resume Next
Range("AN45:AN76").Replace 0, ""
Set R = Range("AN45:AN76").SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
Range("AN45:AN76").Replace "",0
If Not R Is Nothing Then
'rest of code applied to R
End If
End Sub
 

breezer123

New Member
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
This is a little lengthier but I believe it will allow you to have AN45 = 0 or "" and still get all your positive values in the range. If you have the variables declared elsewhere in the code, don't declare them again in the Dim statement or you will get an alert telling you you can't do that.

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

Hm when I tried this and ran it, I got the error "solver encountered an error value in the objective cell or a constraint cell." Right now the cells in that range are either positive values or "". Any ideas?
 

breezer123

New Member
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Here's another possibility that establishes the variable R as a range within AN45:AN76 that excludes cells (including AN45) that have a value of zero.
VBA Code:
Sub IgnoreZeroCells()
Dim R As Range
On Error Resume Next
Range("AN45:AN76").Replace 0, ""
Set R = Range("AN45:AN76").SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
Range("AN45:AN76").Replace "",0
If Not R Is Nothing Then
'rest of code applied to R
End If
End Sub

Thanks so much! Will this work with "" cells as well?
 

breezer123

New Member
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
This is a little lengthier but I believe it will allow you to have AN45 = 0 or "" and still get all your positive values in the range. If you have the variables declared elsewhere in the code, don't declare them again in the Dim statement or you will get an alert telling you you can't do that.

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

One thing I changed in my code was the line

SolverAdd CellRef:="$AN$45:$AN$76", Relation:=3, FormulaText:="$J$2"
to
SolverAdd CellRef:="minrange", Relation:=3, FormulaText:="$J$2"

Where minrange is the range you created the code for earlier. This could be the issue? Is this possible to reference the range within solver this way? Thanks so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,218
Messages
5,640,952
Members
417,182
Latest member
mgcorreia

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top