Find location of a string to use in a For Loop

[zookeepertx]

Hello experts!

I have a macro that works just like I want, with 1 small exception. When I pull the report to work with, the data is organized in a particular number of rows. However, apparently, sometimes the group/file is, say, 26 rows and sometimes it's 51 to the next set of data. As far as I can tell, it's a consistent number for every set of data each time the report is pulled: if it's 26 rows to the end of 1 file # on that day's report, then the entire report has the same # of rows in each file. (Don't ask me why it can change from day to day; I think it's totally crazy)
The last row in a file always says "Show more information" and there's a blank row after that. So, if the file has 25 rows, that row is 25 and I want to establish the next row (the blank one) as the variable for my For Loop.
So, right now my For Loop if the blank row row is 26 my loop would be:

For j = 2 To lr Step 26

But if there happens to be 50 rows in each file, then add 1 for the blank row, the loop would have to be Step 51.

I'm thinking if there was a way to find the blank row # after that first occurrence of "Show more information", I'd like to use the number of that row as a variable to tell the macro what the "Step #" should be.
(It wouldn't need to continue finding that "Show more information" row; just use the row # of the first occurrence as a variable to determine what the "Step #" should be)
For example, if the first "Show more information" is row 25, the blank row is 26, so 26 could be i and then the For Loop could maybe be

For j = 2 to lr Step i

Or something like that.
Clear as mud, right?

Thanks for any help! I feel like I should be able to figure it out but my brain seems to have abandoned me lately.

Jenny

 

[Joe4]

I am not quite sure I understand exactly how your data looks - exactly where blank rows occur and if you are looking for the row with "Show more information", the row after that, or the first blank row after that (this is where am image would really help).

If you always have at least a few rows of data, and they will start on row 2, you can find the last row with data before the first blank row after row 2 for a column (such as column "A") like this:

lr = Cells(2, "A").End(xlDown).Row

If you wanted to find what row the first instance of "Show more information" appears on in column A, you could do that like this:

    lr = Columns("A:A").Find(What:="Show more information", After:=Range("A1"), LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Row

If you wanted to find the last row of data before the the first blank row AFTER "Show more information" (assuming the row right after it is NOT blank), you could do this:

    lr = Columns("A:A").Find(What:="Show more information", After:=Range("A1"), LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Row
        
    lr2 = Cells(lr, "A").End(xlDown).Row

To get the next row after each of these things, simply add 1.

Does that give you what you want?
If not, please post some sample data, and show us what you are looking for.

 

[zookeepertx]

I am not quite sure I understand exactly how your data looks - exactly where blank rows occur and if you are looking for the row with "Show more information", the row after that, or the first blank row after that (this is where am image would really help).

Sorry; I was hoping to give a good explanation. (If I could just get what's in my head to come out my mouth - or in this case, my fingers - I'd make a lot more sense, LOL! )
Here's an image of my data:

I need to locate the row number of the blank cell after "Show more information" - in this case row 26 - but I only need to locate it once and then use the number of that row in a For loop in my macro. For example: For j = 1 to lr Step 26. But each time the report is pulled the data isn't ALWAYS organized in 26-row groups; sometimes 51-row groups. (Maybe other numbers of rows; I'm not sure). But when the report is pulled, they're always the same number of rows within THAT instance of the report.
So, if I can find the number of the blank row after "Show more information" and assign it a variable, can I then use that variable to denote what the "Step ##" should be? Such as "Step i"; with i being the number of the row that's found previously.
Or is it even possible to use a variable in that way: to assign the Step # to be used?

I hope that's clearer. (I tend to use WAY too many words and confuse people. I'm trying to quit that.)

 

[Joe4]

OK, we are going to use the second section code I gave you to find where that wording first appears, and add one to stake the next row after that.
It looks like you are already using the variable "lr" (I presume you already found the last row of data and are storing it there).

So using "i" for the step, like you said, it would look something like this:

Dim i as Long
i = Columns("A:A").Find(What:="Show more information", After:=Range("A1"), LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Row + 1

For j =1 to lr Step i