# Applying a string variable to X number of visible cells from the top.

#### RockandGrohl

##### Active Member
Hello all,

I'm chopping down a phat (with a ph) list of some 500,000 clients in a sheet with filters, and then I have a variable in mind which I want to apply across another variable length of rows.

So Column BA is blank and I need to apply variable "ref" to the top "req" rows, which happens to be 475 in this instance.

Is there a way to take ref and apply it from the top all the way down until it gets to 475, then stops? For the meantime I'm going to do this the suckers way, by implementing a loop with a count that goes down visible cells, but I'm all ears if I can learn a better way to navigate and manipulate filtered data.

Everything I know at the moment tells me I should be defining a range, so in the interest of posterity, my defined range of ("AB2:AB" & LastrowDF) is called "TourPasteRng"

Cheers!

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### RockandGrohl

##### Active Member
Ok I just did 475 cells the suckers way, it took 10 minutes to calculate... y-y-y-y-y-yikes.

Bump

#### Fluff

##### MrExcel MVP, Moderator
A loop shouldn't take that long, unless something else is going on.
This is almost instant
VBA Code:
``````Sub RockandGrohl()
Dim Cl As Range
Dim i As Long

For Each Cl In Range("BA2:BA" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlVisible)
i = i + 1
If i <= 475 Then Cl.Value = "Abc" Else Exit For
Next Cl
End Sub``````

#### RockandGrohl

##### Active Member

Hi Fluff,

Agreed, probably a wanton formula hiding somewhere - from a sheet passed to me and not one I made myself.

That looks exactly what I'm after, will give it a shot and let you know, thanks.

#### RockandGrohl

##### Active Member
Hi Fluff, worked beautifully and quickly as advertised. It takes 5 minutes to fill 80,000 rows out of 380,000 or so - I do a fair bit of looping in between the region criteria which I t hen use to get the required volumes.

One thing to note, I had to add a i=0 onto the end of the loop because the next time around it was keeping i as 475 or whatever. I guess I didn't mention strictly it would be in a loop and I would be performing the action multiple times as per the variable.

Anyway, works basically instantly, thank you!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
6
Views
93
Replies
7
Views
328
Replies
11
Views
508
Replies
0
Views
268
Replies
1
Views
7K