Searching for greater than 1 value in a column Excel VBA

Question

Anybody knows here how to look for a value that is greater than 1 in a column?

Set fileSheet = wb.ActiveSheet
fileSheet.Name = "Test"


Set rng = fileSheet.Range("D:D")
Set rngFound = rng.Find(">1")

If rngFound Is Nothing Then
    MsgBox "No value"
End If

I'm trying to do it that way, but I know that if you put a double qoute, it will be treated as a String, so is there any way I can look for a greater than 1 value before filtering it? Please note that I will be working with a column that has thousands of data.

You can't use Find that way. Probably have to loop, perhaps using specialcells to limit the range to be searched. Or could add a helper column and filter on that. — SJR, Aug 18 '17 at 13:24
@SJR Owhh, if I have to loop, it might take some time, and can cause the excel to freeze. — , Aug 18 '17 at 13:28
Use filtering, that is decent up to a tens of thousands of rows. Record macro of an autofilter, and work your way up from there. — vacip, Aug 18 '17 at 13:32
@SJR Nope, I don't have to sort the column, all I need is to check if the column has >1 value :) — , Aug 18 '17 at 13:33
Suggested code below. What are you actually trying to achieve? — SJR, Aug 18 '17 at 13:33
@SJR I have to find values that is greater than 1, if it has greater than 1 values, then filter and apply a formula. May be I'm going to create a function to check if the column has >1 value — , Aug 18 '17 at 13:36
Why not just use autofilter then? Or do you have blank rows? — SJR, Aug 18 '17 at 13:37

score 2 · Answer 1 · answered Aug 18 '17 at 13:33

This should speed things up, assuming the cells contain numbers rather than formulas. An array would be much quicker, but depends what exactly you are trying to achieve.

Sub x()

Dim r As Range, filesheet As Worksheet, Rng As Range

Set filesheet = wb.ActiveSheet
filesheet.Name = "Test"

Set Rng = filesheet.Range("D1", filesheet.Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, xlNumbers)

For Each r In Rng
    If r.Value > 1 Then
        'do whatever
    End If
Next r

End Sub

score 0 · Answer 2 · answered Aug 18 '17 at 13:27

0

Use two for cycles for the index of last column and row like this. Or, use the active sheet's range's foreach cycle. More info: How to iterate through all the cells in Excel VBA or VSTO 2005

answered Aug 18 '17 at 13:27

Ádám Géczi

166
5

sourceCode · Answer 3 · 2017-08-18T14:00:18.957

0

displays cells that are > 1 for the range D1 to D100

Dim i As Integer

With ThisWorkbook.Sheets(1)

For i = 1 To 100

    If .Cells(i,4).Value > 1 Then
        MsgBox "Value Found! Cell D " & i
    End If

Next i

End With

edited Aug 18 '17 at 14:00

answered Aug 18 '17 at 13:28

sourceCode

338
4
20

Thanks. But I file has thousands of rows with data. This may cause the file to crash. – Aug 18 '17 at 13:29
give it a try and let me know what happends – sourceCode Aug 18 '17 at 13:30
This works, but is not applicable for a file that has thousands of data – Aug 18 '17 at 13:31
1

You never specified that in the question – sourceCode Aug 18 '17 at 13:32
Doesn't this check the same range 100 times? You don't use `i` in the checking, only the message. The `>1` is always looking at the same range. – vacip Aug 18 '17 at 13:33
@sourceCode Updated my question :) – Aug 18 '17 at 13:34

Searching for greater than 1 value in a column Excel VBA

3 Answers3