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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
Hi all, venturing into the wild world of For Each instead of Do Until loops, have the following:

Code:
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
What's the value of LastrowAD when the code runs?
 
Upvote 0
That should work, what is the value of LastrowAD?
 
Upvote 0
I've found that, when looping through ranges, its best to specify what you are looping through

Code:
For Each cl In visrng.SpecialCells(xlCellTypeVisible)[COLOR="#FF0000"].Cells[/COLOR]
(as opposed to .Row, .Columns. or .Areas)

I'd add to RoryA's question, what is the address of visrng.SpecialCells(xlCellTypeVisible)?
 
Upvote 0
What's the value of LastrowAD when the code runs?


28,433 :)

I've got it currently set to "Cells(Activecell.row, "W").value = "DONK" "

In the For Each loop

So I get a nice fat DONK in cell W26 as that's the first visible one, but nothing in W70, 71, 72, 73, 74 and so on.
 
Upvote 0
I've found that, when looping through ranges, its best to specify what you are looping through

Code:
For Each cl In visrng.SpecialCells(xlCellTypeVisible)[COLOR=#FF0000].Cells[/COLOR]
(as opposed to .Row, .Columns. or .Areas)

I'd add to RoryA's question, what is the address of visrng.SpecialCells(xlCellTypeVisible)?


Thanks, that top bit doesn't work.

To answer your second, when hovering over visrng it doesn't display anything.
 
Upvote 0
How many rows are visible?
 
Upvote 0
28,433 :)

I've got it currently set to "Cells(Activecell.row, "W").value = "DONK" "

In the For Each loop

So I get a nice fat DONK in cell W26 as that's the first visible one, but nothing in W70, 71, 72, 73, 74 and so on.

That is because the ActiveCell is the same in every loop.

Try putting
Code:
cl.Offset(0, 22).Value = "Donk"
inside the loop
 
Last edited:
Upvote 0
That should work, what is the value of LastrowAD?

If I do this:

Code:
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).Cells
    MsgBox cl.Address
Next cl

It correctly shows the address of the filtered cells - A26, A70, A71 etc.

So it is looping, but I have no idea the syntax to get the left 6 or checking the len if I can't use ActiveCell.

I'm trying to transcend above activecell and activating and writing one cell at a time as I want to develop my VBA skills.
 
Upvote 0
That is because the ActiveCell is the same in every loop.

Try putting
Code:
cl.Offset(0, 22).Value = "Donk"
inside the loop

Thanks, think I'm getting closer, how do I get it to write to column W though?

EDIT: Gold star please, I did it (nearly) all by myself!

Code:
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).Cells
    If Len(Range("A" & cl.Row)) < 7 Then
    Range("W" & cl.Row).Value = Range("A" & cl.Row).Value
    Else
    Range("W" & cl.Row).Value = Left(Range("A" & cl.Row).Value, 6)
    End If
Next cl
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top