# Use only visible rows in Macros code after autofilter

#### Namratak

##### New Member
Hi,
I have coded for a solver problem in macros. But the problem is the range in B column is auto filtered and the other rows are hidden. So as per inputs, only the rows in B column that satisfy the input are shown. It can start at any row. How do I include the code to use only visible cells in the column B for calculation? Pls help.

Dim Counter As Integer
Dim lastrow As String

Counter = 0
For Each Testname In Range("B3:B10000")
If IsEmpty(Testname) = False Then
Counter = Counter + 1

Else
Counter = Counter + 0
End If

Next Testname
SolverOk SetCell:="\$T\$1", MaxMinVal:=2, ValueOf:=0, ByChange:="\$L\$3:\$L\$counter", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="\$T\$1", MaxMinVal:=2, ValueOf:=0, ByChange:="\$L\$3:\$L\$counter", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### JLGWhiz

##### Well-known Member
VBA Code:
``For Each Testname In Range("B3", Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeVisible)``

#### Namratak

##### New Member
Hi, this works. thankyou somuch! however I had another doubt. I wanted to calculate column L of no. of units needed of each type of row such that minimum cost occurs( if cost is cell T1) and the column G should be equal to or more than the column L that the macros calculated.
However my code just distributes the cost among all the visible rows. where am I going wrong in the coding?

#### Namratak

##### New Member
this is the nearest ive come to:

VBA Code:
``````Sub Macro4()
'
' Macro4 Macro
'
'
Dim Counter As Integer
Dim lastrow As String
Counter = 0
For Each Testname In Range("B3", Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeVisible)
If IsEmpty(Testname) = False Then
Counter = Counter + 1

Else
Counter = Counter + 0
End If

Next Testname
SolverOk SetCell:="\$T\$1", MaxMinVal:=2, ValueOf:=0, ByChange:="\$L\$3:\$L\$counter", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub``````

#### JLGWhiz

##### Well-known Member
I can't be much help on the Solver, I have never used it and have no idea how it works.
Regards, JLG

Replies
2
Views
198
Replies
1
Views
612
Replies
1
Views
347
Replies
1
Views
295
Replies
0
Views
253

1,127,524
Messages
5,625,309
Members
416,092
Latest member
dodovisk

### 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