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

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:
Code:
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'
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,617
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
For Each cell In rng_pdasrv.Columns(1).Rows
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How about
VBA Code:
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)
Rich (BB code):
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
Code:
 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,617
Office Version
  1. 365
Platform
  1. Windows
You can do it like
VBA Code:
   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

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Many thanks Fluff! There are a lot of new concepts in your solution meaning great educational opportunity.
Code:
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
Code:
DelRng.EntireRow.Delete
is what i want to do.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,617
Office Version
  1. 365
Platform
  1. Windows
In that case use
VBA Code:
If Not DelRng Is Nothing Then Intersect(DelRng.EntireRow, rng_pdasrv).Delete
 
Solution

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,617
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
Please start a new thread for your new question.
 

Forum statistics

Threads
1,147,571
Messages
5,741,887
Members
423,693
Latest member
Excelquestion35

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top