Excel VBA - Ubound count array until row with specific text

Question

I have a sheet with 4 columns (Name,Age,Last name, amount) and has 30 entries. heading row1 and 30 entries. So cell (32,1) has text total and summation of amount is available in corresponding Amount column. I have named range the data. I am trying to collect the row number by using Ubound. My condition is I should get the row count until I find Total text (say only until row 31).

Here is my code.

Option Explicit

Sub calculate()

Dim rowcount As Long
Dim rng As Range
Dim ws As Worksheet

ws = ThisWorkbook.Sheets("Sheet1")
rng = ws.Range("DataAmnt")

ws.Activate
rowcount = UBound(rng, 1)

End Sub

Apologies I do not have reputation to share picture of worksheet.

So what's your question? You've stated what you want to do and you've even provided some code to that effect, but what do you want us to help you with? — Aiken
rowcount should ignore the last row which has text "Total". The data is subjected to be dynamic and row count will be dynamic.But the sturcture remains the same. — user4684412
That's still not a question, you're just telling us your requirements which makes it sound like you just want someone to write/finish your code for you. We're a Question and Answer site, not volunteer programmers. You'll need to tell us what specific problem you're facing in implementing this yourself, are you getting an error? Incorrect output? — Aiken
i am unable to get the requirement by any means like find string and other ways. The above code works fine to get me total row count. Here is where i need someone to help me. If i have a code, i dont require a post here. — user4684412
Okay, this is the last comment I'm going to make here. You're repeatedly stating your requirements/objectives, but we don't want your software spec, we want a specific, answerable question. So I ask one more time, as plainly as I can: What. Is. Your. Question? Don't reply in comments, edit your question so that it's exactly that, a question, not an open-ended statement of your requirements. — Aiken

izzymo izzymo · Accepted Answer · 2015-03-24T11:09:09

The below code should loop through each cell and get you the row of the cell that has the words total in it. An alternative can be Cells.Find method which is quicker if you have a smaller range.

Option Explicit

Sub calculate()

Dim rowcount As Long
Dim rng As Range
Dim ws As Worksheet

ws = ThisWorkbook.Sheets("Sheet1")
rng = ws.Range("DataAmnt")

ws.Activate

for each cell in rng

    if cell.value = "Total text" then 
        rowcount = cell.row
        exit for
    end if

next cell

End Sub

Excel VBA - Ubound count array until row with specific text

1 Answers