Looping thru values in a Range of Cells

[Guest]

Hi All

We have a requirement whereby we would like to loop-thru range of cells,
read the values from these cell range and then do certain action.
For example, we will define list of values in a column say C5:C25
......D525; see example below
---------------------------
C D
---------------------------
5 AgentID 10
6 Agentname 25
7 Address 20
........

What we would like to do is
1. Read the Cells one by one starting [C5 and D5] , then [C6 and D6] and
then [C7 and D7]...
2. The value in C5 will be the Column in Pivot table
The value in D5 will be the Column Length for that column
3. Repeat the same (in-loop) for C6 and D6......... C25 and D25
4. The only catch is, the MAX range is un-predictable, it could be C5 - C10
for one report and C5-C15 for others.

Is this in any way achieveable? I know we can give some "Virtual Name" for
the cells of the excel, not sure if I can do that for range of Cells.

Thanks
Prasanna

 

[Kaak]

Is this any help...?

LastRow = Range("C5").End(xlDown).Row

For i = 5 To LastRow

ColumnNr = Range("C" & i).Value
ColumnLn = Range("D" & i).Value

Next

 

[Guest]

Hi Kaak

What you said is defenitely helping me. But ".End(xlDown)" cannnot suffice
my requirement 100%. The problem we have is as follows"

Row 5-25 will be for Group-1, Row # 26 will be some "Header Row", 27-35 may
be Data for Group-2....; I short data can go up as many rows as we want and
which are then logically split into groups within it. Each group of Data
goes in different section of the PIVOT Table. The below picture might
explain what I'm saying.

---------------------------
C D
--------------------------- Row 5-25 is Data for Group1
5 AgentID 10
6 Agentname 25
7 Address 20
........
25
----------------------------------------------------------
26 Some Header to distinguish the Group(s)
--------------------------- Row 27-35 is Data for Group2
27 PoNumber 10
28 PODate 15
29 ShipToAddress 50
........
35
.......

What I want is 2 while loops, first one from Row 5 - Row 25, second one from
Row 27 - Row 35. Though I have trold you the exact row numbers now, it wont
be true always, the row numbers can be more or less (it's dynamically
changing from report to report).

Hope this explains
Thanks
PP