How to generate a range variable w/ set columns but multiple rows

[crashcb18]

All,
I am learning the range variable as an object, i.e.
dim example As Range

Ok, I have a spreadsheet with 100 rows and columns of info (*B:*AB for example). Each row has a "header" text in column A. I want to write a short subroutine that looks through "A" column, row by row, and for every row that matches the if statement, then it'll add that row to a range variable ("totalrows" for example). That way, I have a new range variable ("totalrows") in which I have a "For Each "Cell" in "range" for the macro to do it's function on.

For example of logic:

For each cell in Column A
If cell = desiredcell then
add the row of that cell to a range variable in which columns are_
already set from B:AB but I want to add that specific row (in the end_
I'll have a range variable with range: "10A:10AB, 14A:14AB,_
24A:24AB,...."

Else
exit If loop (back to "for each cell in Column A" loop)
For each cell in "newrange"
do rest of evaluation code for each cell, I already have this portion written


Does htat make sense?

 

[Norie]

Why don't you just do the whole thing in the one loop?

 

[crashcb18]

I basically have modules of rows. For example, each module is a collection of 3 or 5 rows of 141 columns. Each row in that module has different data being evaluated. If i ever add or delete rows to edit a module, then I have to recount all the rows so that I can specify which row to perform an offset evaluation on.

Make sense? I'm trying to save myself from having to count through the rows and write down which rows to manually type into the code.

 

[Norie]

Sorry I don't follow.

Why can't you go through the rows, identify the ones of interest, do whatever it is you need to do and then move on.

 

[crashcb18]

ok, then lets say I have an if loop that goes through each cell in a column to find the rows of interest. How do I pass that particular row number to the subroutine to evaluate?

 

[mikerickson]

Try a loop like this

Dim myRange as Range, oneCell as Range

Rem seed the range with a cell outside of the result ranges.
Set myRange = Range("BB1")

For Each oneCell in Range(Range("A1"), Range("A65536").End(xlup))
    If oneCell.Value = "x" Then
        Set myRange = Application.Union(myRange, oneCell.EntireRow.Range("A1:AB1"))
    End If
Next oneCell

Rem remove seed value
Set myRange = Application.Intersect(myRange, Range("A:AB"))

 

[Norie]

If you are calling a sub you can use this to pass a range reference to it from the loop.

You can get all the properties of the range, resize it, offset from it etc.

' initial loop
For Each cl In Range("A1:A100")
 
       If cl.Value = 'your criteria' Then
 
               Call EvalSub(cl)
       End If
Next cl
 
Private Sub EvalSub(rngEval As Range) 
 
     MgsBox "Evaluating row " &clEval.Row 
 
        ' do stuff with rngEval range reference
End Sub