No sure why this isn't working

[SITCFanTN]

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

[Paul Mathews]

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
>
>

[Paul Mathews]

Whoops sorry, ignore the Range("A1:A10"), I just put that in there to test

"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
>
>

[SITCFanTN]

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
> >
> >

[Paul Mathews]

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
> > >
> > >