Select every cell that has a certain value

[Arne Hegefors]

I want to select every cell in an orderd column that has value 0. Is this
possible and how would you do it? First I search the spreadsheet in order to
find the cell that signals the start of the list. The code is:

Set relativCell = Worksheets("Beräkning").Cells.Find("Rel.", LookIn:=xlValues)

I then want to search through every value below relativCell and see if they
are equal to zero. I have ensured that it is a ordered list so you really
just need the first and the last cell that is equal to zero. I search through
the list like this:

Do Until IsEmpty(relativCell.Offset(i, 0)) = True
i = i + 1 'längst ner i listan
Loop

Can someone please help me with how you find the range for the zero values?
what i want to do is to give the user the opportunity to hide all rows that
has value equal to zero.. Thanks!

 

[Jim Thomlinson]

Just use find and FindNext, something like this...

Sub FindZero()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = Sheets("Beräkning")
Set rngToSearch = wks.Cells
Set rngFound = rngToSearch.Find(What:=0, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.select
End If
End Sub