Hi again, one approach would be to define a data count variable and use the
CountA worksheet function to figure out how many non-blank items are in the
column, e.g., to figure out how many non-blank items are listed in column A:

Dim i as Integer, DataCount as Integer

DataCount = Application.WorksheetFunction.CountA(Columns(1))

For i = 1 to DataCount
Blah Blah
Next i

The only caveat here is that you'll need to get rid of any embedded blank
items in the list (you need a continuous list of non-blank items).

"SITCFanTN" wrote:

Hi Paul,

My range length is not definate. The report can be 50 rows or 5000 rows.
How would I code that? Just ("A:A")

"Paul Mathews" wrote:

Is it possible that you need your code to look like this:

Set rng = Range("A1:A10")
For Each cell In rng
If Len(cell.Value) 7 Then
cell.Offset(0, 1).Value = cell.Value
cell.ClearContents
End If
Next


"SITCFanTN" wrote:

Here is the code that I used in a sheet to move cell contents from one row to
another. I'm using in a another sheet and it isn't working....it is moving
the entire row to the new column, not just the cells with greater than 7
characters. Any ideas? Thanks

Set rng = Range(Cells(2, "K"), Cells(Rows.Count, "K").End(xlUp))
For Each cell In rng
If Len(cell.Value) 7 Then
cell.Offset(0, 1).Value = cell.Value
Next
cell.ClearContents
End If