Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping thru a range of cells | New Users to Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
looping across columns in range? | Excel Discussion (Misc queries) | |||
looping through a range | Excel Programming | |||
looping cells though a named range | Excel Programming |