Loop Through Cells of a Column of a Dynamic range of cells

[Ark68]

I hope I can explain this well enough to allow someone to help.

I have defined variable 'rng_pdasrv' as a range. It represents a dynamic range "A22:Q32".
I simple wish to loop through each cell in column A within that range, ie A22:A32.
ie:

For each cell in A22 to A32
     If cell.value = "Name" then call sub5
Next cell

Incethe range is dynamic, I need to figure out how to code the paramters of the loop based on the dynamic values of the data in the range 'rng_pdasrv'

 

[Fluff]

How about

For Each cell In rng_pdasrv.Columns(1).Rows

 

[Ark68]

How about

For Each cell In rng_pdasrv.Columns(1).Rows

Thank you Fluff, but I just realized that my explanation wasn't fully accurate.

My code will basically be like this (with your suggestion)

For Each cell In rng_pdasrv.Columns(1).Rows
       if cell.value = crid then
              delete the range A:Q of that row and shift everything up
       endif
Next cell

What I have learned that if one is looping through rows with the indent to delete them, you have to step backwards through the range.
ie

 for cell = A32 to A22 step -1
delete range A:Q
next cell

That being said, will your suggestion still work? And with this method, how would I get the value of the row that matches the if statement to delete it?

 

[Fluff]

You can do it like

   Dim DelRng As Range
   For Each cell In rng_pdasrv.Columns(1).Rows
      If cell.Value = crid Then
         If DelRng Is Nothing Then Set DelRng = cell Else Set DelRng = Union(DelRng, cell)
      End If
   Next cell
   If Not DelRng Is Nothing Then DelRng.EntireRow.Delete

 

[Ark68]

Many thanks Fluff! There are a lot of new concepts in your solution meaning great educational opportunity.

If DelRng Is Nothing Then Set DelRng = cell Else Set DelRng = Union(DelRng, cell)

admittingly eludes my understanding.

I don't want to delete the entire row though, just the columns A:C of that row, so I don't think

DelRng.EntireRow.Delete

is what i want to do.

 

[Fluff]

In that case use

If Not DelRng Is Nothing Then Intersect(DelRng.EntireRow, rng_pdasrv).Delete

 

[Ark68]

Thanks Fluff! Thats a pretty sweet routine. It took me a bit to figure out why it wasn't working but once I did it's awesome! I'm still plugging away with how to fully appreciate how it works. I'm not used to working within defined ranges.

May I ask for one more helping hand. That same range (rng_pdasrv), how can i find which row in column A of that range is the last cell holding a value?

 

[Fluff]

You're welcome & thanks for the feedback.
Please start a new thread for your new question.