CODEWITHSUNDEEP
vbaexcelms-office
Sinros
Sinros

Reputation: 181

Excel VBA - Find all rows with a specific value and get their row number

I have close to zero knowledge in excel and vba. What I'm trying to do the the following:

for each row in ActiveSheet.ListObjects("SheetPotatoData")
    if cell (column 5):(row) value equals "potato"
        do something with (column 2):(row)

I would really appreciate it if you could enlighten me on the proper syntax to do this.

Thank you very much!

Upvotes: 2

Views: 27905

Answers (2)

PairrieNerd403
PairrieNerd403

Reputation: 61

Alternatively, just add ".DataBodyRange.Rows" to the end of the first line in your line "For... Each" structure. You're asking VBA to iterate through each Row in a ListObject, rather each Row in the collection of Rows contained within the ListObject.

That allows sticking with the more elegant, readable, and reliable 'For...Each' structure rather than fetching range bounds and traversing it using counters. Using the DataBodyRange property of the list allows you to automatically handle any Header rows that may be present and not include them in your row inspection loop. You can reference the ListObject.Rows collection instead if you want Headers included.

Final code would look something like:

For Each row In ActiveSheet.ListObjects("SheetPotatoData").DataBodyRows.Rows
    if row.Cells(1,5) = "potato"
        'Do something
    End If
Next

Upvotes: 0

Daniel Lee
Daniel Lee

Reputation: 8011

Look here: http://www.excel-easy.com/vba/range-object.html 

For i = 1 To ActiveSheet.ListObjects("TableNameHere").ListRows.Count
    If Cells(i,5).Value = "potato" Then
        Cells(i,2).Value = "New value"
    End If
Next

Upvotes: 4

Related Questions