For Each Visible cell in range, get me the left 6 characters

[RockandGrohl]

Hi all, venturing into the wild world of For Each instead of Do Until loops, have the following:

Dim LastrowAD As LongDim cl As Range
Dim visrng As Range


LastrowAD = Cells(Rows.Count, "A").End(xlUp).Row


Set visrng = Range("A2:A" & LastrowAD)


For Each cl In visrng.SpecialCells(xlCellTypeVisible)
   '>>IF LEN OF CELL IN COLUMN A IS >6, THEN GET THE LEFT 6 CHARACTERS, OTHERWISE JUST REPEAT THE CELL<<
Next

What I'm struggling with is how to get it to loop through each visible cell. It seems to do the first visible cell, but no others in the range?

I'm sure once I've got that, I can just put in the IF LEN blah blah blah part.

Cheers!

 

[RoryA]

That is writing to column W. cl is the visible cell at each iteration, and 22 columns to the right of column A is column W.

 

[mikerickson]

ActiveCell is a property of the worksheet, that won't change.

You should be using cl, the variable that your code has looping through the range of visible cells.

Instead of cl.Address, you could be testing Left(cl.Value, 6)

 

[mikerickson]

I'm glad you got it sorted out. But I noticed something new.

You don't need to test for the length. Since Left will take the leftmost characters UP TILL the second argument (i.e. Left("abc",6) = "abc"), you can just use this, with no time consuming test.

For Each cl In visrng.SpecialCells(xlCellTypeVisible).Cells
    Range("W" & cl.Row).Value = Left(Range("A" & cl.Row).Value, 6)
Next cl

Also, when I use range objects, I don't like to keep taking their row or column properties. (I also like the With...End With construction, stylisticly), you might try

For Each cl In visrng.SpecialCells(xlCellTypeVisible).Cells
    With cl
        .EntireRow.Range("W1").Value = Left(.Value, 6)
    End With
Next cl

 

[Rick Rothstein]

Also, when I use range objects, I don't like to keep taking their row or column properties.

Another way to do it...

Sub GetLeftSixCharactersFromVisibleCellsInColumnA()
  Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlVisible).Areas
    Ar.Offset(, 22) = Evaluate("IF({1},LEFT(" & Ar.Address & ",6))")
  Next
End Sub