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

[RockandGrohl]

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!

 

[RockandGrohl]

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

 

[RockandGrohl]

Bump

 

[Fluff]

A loop shouldn't take that long, unless something else is going on.
This is almost instant

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]

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]

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]

You're welcome & thanks for the feedback.