Need help with looping through a dynamic range
 

Windows 2k Pro
Excel 2000

I have four columns that contain the various elements of a road name.

Column A contains PREFIX values
Column B contains NAME values
Column C contains SUFFIX values
Column D contains POST DIRECTIONAL values

All values are Strings and each column begins at Row 2 with the values (Row
1 contains headers)

There may or may not be a value in any particular cell.

The total number of rows can vary depending on which list I am evaluating.

What I'd like to do is loop through the list and concatenate the four values
in each row into a single value and display them Column E. While I'm at it
I might as well store the concatenated values in an array I'll call LIST(i).

I know how to concatenate the separate values into a single value, but the
looping is causing me some headaches. As I see it the trick is to figure
out how many rows there are in total so that I know how to set the loop up.

Would you try to do a For-Next loop or maybe a For Each cell loop? I'm not
asking anybody to do this for me, I'm just looking for a nudge in the right
direction.

Any hints or suggestions greatly appreciated.

-gk-

Need help with looping through a dynamic range
 

Something like (untested, off the top of my head):

Dim c As Range
Do While WorksheetFunction.CountA(c.EntireRow) < 0
For Each c In Range("A2:A65536")
'your code
Next
Loop

"TBA" wrote in message
...
Windows 2k Pro
Excel 2000

I have four columns that contain the various elements of a road name.

Column A contains PREFIX values
Column B contains NAME values
Column C contains SUFFIX values
Column D contains POST DIRECTIONAL values

All values are Strings and each column begins at Row 2 with the values
(Row
1 contains headers)

There may or may not be a value in any particular cell.

The total number of rows can vary depending on which list I am evaluating.

What I'd like to do is loop through the list and concatenate the four
values
in each row into a single value and display them Column E. While I'm at
it
I might as well store the concatenated values in an array I'll call
LIST(i).

I know how to concatenate the separate values into a single value, but the
looping is causing me some headaches. As I see it the trick is to figure
out how many rows there are in total so that I know how to set the loop
up.

Would you try to do a For-Next loop or maybe a For Each cell loop? I'm
not
asking anybody to do this for me, I'm just looking for a nudge in the
right
direction.

Any hints or suggestions greatly appreciated.

-gk-

Need help with looping through a dynamic range
 

Vasant,

Your suggesion won't work properly if column A is empty in the last row, and
you test c before you set it.

Here's an alternative that addresses both points (I hope <G). As it is only
4 columns I concatenate in one statement. A loop would be better if it were
many more, or an unknown amount.

Dim cLastCol As Long, cLastRow As Long, i As Long
Dim rng As Range, c As Range
Dim oRow As Range

ActiveSheet.UsedRange
With ActiveSheet.Cells.SpecialCells(xlLastCell)
cLastCol = .Column
cLastRow = .Row
End With

Set rng = Range("A2", Cells(cLastRow, "D"))
For Each oRow In rng.Rows
If WorksheetFunction.CountA(oRow) < 0 Then
Cells(oRow.Row, "E") = Cells(oRow.Row, "A") & Cells(oRow.Row,
"B") & _
Cells(oRow.Row, "C") & Cells(oRow.Row,
"D")
End If
Next


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Something like (untested, off the top of my head):

Dim c As Range
Do While WorksheetFunction.CountA(c.EntireRow) < 0
For Each c In Range("A2:A65536")
'your code
Next
Loop

"TBA" wrote in message
...
Windows 2k Pro
Excel 2000

I have four columns that contain the various elements of a road name.

Column A contains PREFIX values
Column B contains NAME values
Column C contains SUFFIX values
Column D contains POST DIRECTIONAL values

All values are Strings and each column begins at Row 2 with the values
(Row
1 contains headers)

There may or may not be a value in any particular cell.

The total number of rows can vary depending on which list I am
evaluating.

What I'd like to do is loop through the list and concatenate the four
values
in each row into a single value and display them Column E. While I'm at
it
I might as well store the concatenated values in an array I'll call
LIST(i).

I know how to concatenate the separate values into a single value, but
the
looping is causing me some headaches. As I see it the trick is to
figure
out how many rows there are in total so that I know how to set the loop
up.

Would you try to do a For-Next loop or maybe a For Each cell loop? I'm
not
asking anybody to do this for me, I'm just looking for a nudge in the
right
direction.

Any hints or suggestions greatly appreciated.

-gk-

Need help with looping through a dynamic range
 

Hi Bob:

"Bob Phillips" ... wrote ...
Your suggesion won't work properly if column A is empty in the last row,
and
you test c before you set it.<<

"Vasant Nanavati" ... wrote ...
(untested, off the top of my head)<<

Why do you think I put in so many disclaimers in there? <vbg

--

Vasant


"Bob Phillips" wrote in message
...
Vasant,

Your suggesion won't work properly if column A is empty in the last row,
and
you test c before you set it.

Here's an alternative that addresses both points (I hope <G). As it is
only
4 columns I concatenate in one statement. A loop would be better if it
were
many more, or an unknown amount.

Dim cLastCol As Long, cLastRow As Long, i As Long
Dim rng As Range, c As Range
Dim oRow As Range

ActiveSheet.UsedRange
With ActiveSheet.Cells.SpecialCells(xlLastCell)
cLastCol = .Column
cLastRow = .Row
End With

Set rng = Range("A2", Cells(cLastRow, "D"))
For Each oRow In rng.Rows
If WorksheetFunction.CountA(oRow) < 0 Then
Cells(oRow.Row, "E") = Cells(oRow.Row, "A") & Cells(oRow.Row,
"B") & _
Cells(oRow.Row, "C") & Cells(oRow.Row,
"D")
End If
Next


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Something like (untested, off the top of my head):

Dim c As Range
Do While WorksheetFunction.CountA(c.EntireRow) < 0
For Each c In Range("A2:A65536")
'your code
Next
Loop

"TBA" wrote in message
...
Windows 2k Pro
Excel 2000

I have four columns that contain the various elements of a road name.

Column A contains PREFIX values
Column B contains NAME values
Column C contains SUFFIX values
Column D contains POST DIRECTIONAL values

All values are Strings and each column begins at Row 2 with the values
(Row
1 contains headers)

There may or may not be a value in any particular cell.

The total number of rows can vary depending on which list I am
evaluating.

What I'd like to do is loop through the list and concatenate the four
values
in each row into a single value and display them Column E. While I'm
at
it
I might as well store the concatenated values in an array I'll call
LIST(i).

I know how to concatenate the separate values into a single value, but
the
looping is causing me some headaches. As I see it the trick is to
figure
out how many rows there are in total so that I know how to set the
loop
up.

Would you try to do a For-Next loop or maybe a For Each cell loop?
I'm
not
asking anybody to do this for me, I'm just looking for a nudge in the
right
direction.

Any hints or suggestions greatly appreciated.

-gk-