Need help with looping through a dynamic range

[TBA]

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-

 

[Vasant Nanavati]

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

 

[Bob Phillips]

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]

Hi Bob:

"Bob Phillips" ... wrote ...and
you test c before you set it.<<

"Vasant Nanavati" ... wrote ...
Why do you think I put in so many disclaimers in there? <vbg>