CODEWITHSUNDEEP
excelrangevba
vgupta22
vgupta22

Reputation: 60

Use FindNext on a Range of Variable Length in Excel VBA

I'm fairly next to excel VBA and am trying to use the FindNext function to find the next non-zero value in a column on the following data set.
enter image description here
When I first tried to do this I used the Columns function saying: Columns("J").FindNext().Row but returned the value of 3 rather than 11. Then I tried tried to use the range object, declaring it as Range("J2", Cells(Cells(Rows.Count, "H").End(xlUp).Row, "J")) where Column H has data till the length of Column J. doing this returned the error "Compile Error: Wrong number of Arguments or invalid property assignment" I'm not entirely sure why 1) the compiler wont accept how I declared the Range and 2) why the FindNext function is not returning the row of the next non-zero value. Thank you in advanced.

Upvotes: 0

Views: 282

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

You do not need either Find() or FindNext() to locate the rows containing non-empties:

Sub FindingAnything()
    Dim rng As Range
    Dim s As String

    Set rng = Range("J:J").Cells.SpecialCells(xlCellTypeConstants)

    For Each r In rng
        s = s & r.Row & vbCrLf
    Next r

    MsgBox s
End Sub

enter image description here

If the column contained some cells with formulas, you would change the SpecialCells() argument. If the column was filled with formulas, some of which return non-Nulls, you would have an interesting problem !

Upvotes: 3

Related Questions