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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
491
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
491
Office Version
  1. 2010
Platform
  1. Windows
Ok I just did 475 cells the suckers way, it took 10 minutes to calculate... y-y-y-y-y-yikes.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 1, 2018
Messages
491
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 1, 2018
Messages
491
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,035
Members
410,721
Latest member
adi772
Top