VBA filling loop

[mole999]

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

 

[MARK858]

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]

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]

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

Book1
	Y
10	ON
11
12
13
14
15
16
17
18
19
20	OFF
21
22	ON
23
24
25	ON
26
27	OFF
28
29
30	ON
Sheet4

 

[mole999]

11-19 ON, 23-24 ON, 26 ON, other remain blank

 

[Fluff]

How about

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

 

[MARK858]

Hi Fluff, the code didn't put an "ON" in Y26 for me.

 

[Fluff]

Are you sure Y26 is blank, no spaces or anything like that?

 

[MARK858]

=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

 

[mole999]

Thany you both.Blindingly quick, apart from a few initial stumbles whilst testing