VBA filling loop

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,899
I have a doc with 90000 rows. I need to visualise a status.

I.e row 10 has on, then row 20 has off, so 11 to 20 have the ON flag and is visible.

trying to do this manually and it is taking for ever to identify the start point, double click so it fills down

The statuses are in Y, and i want AA to show SO as the output for every entry between 10 and 20, for the 90000

Ideas appreciated.

I will then change to suit other necessary highlights.


Just a thought I think I saw a @Fluff routine, fill to totals or something like that, which i will look for in the meantime
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,487
Office Version
365, 2010
Platform
Windows, Mobile
Are you just saying that you want to fill the blank cells in-between and then filter on the "ON" cells? does the "OFF" in Y20 then fill down to the next "ON"?
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,899
Are you just saying that you want to fill the blank cells in-between and then filter on the "ON" cells? does the "OFF" in Y20 then fill down to the next "ON"?
sort of, need to fill down from ON to the OFF, then find the next ON and do again to the end of 90000
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,487
Office Version
365, 2010
Platform
Windows, Mobile
Just to be clear what would you want the below to look like in terms of the filling. Incuding if any bits aren't possible in your data

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>Y</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">ON</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">OFF</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">ON</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">ON </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">OFF</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style=";">ON</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br />
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,899
11-19 ON, 23-24 ON, 26 ON, other remain blank
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,206
Office Version
365
Platform
Windows
How about
Code:
Sub Mole999()
    Dim Rng As Range
    
    For Each Rng In Range("Y:Y").SpecialCells(xlBlanks).Areas
        If LCase(Rng.Offset(-1).Resize(1).Value) = "on" Then Rng.Value = "ON"
    Next Rng
End Sub
This assumes that you have a value in Y1
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,487
Office Version
365, 2010
Platform
Windows, Mobile
Hi Fluff, the code didn't put an "ON" in Y26 for me.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,206
Office Version
365
Platform
Windows
Are you sure Y26 is blank, no spaces or anything like that?
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,487
Office Version
365, 2010
Platform
Windows, Mobile
=ISBLANK(Y26) returned TRUE and LEN(Y26) returned 0 but ran the code again after re-copying the data from post 4 and it filled Y26.... confused but not going to get to the bottom of it as it now is working :banghead:
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,899
Thany you both.Blindingly quick, apart from a few initial stumbles whilst testing
 

Forum statistics

Threads
1,081,971
Messages
5,362,492
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top