VBA filling loop

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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"?
 
Upvote 0
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
 
Upvote 0
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
10ON
11
12
13
14
15
16
17
18
19
20OFF
21
22ON
23
24
25ON
26
27OFF
28
29
30ON
Sheet4
 
Upvote 0
11-19 ON, 23-24 ON, 26 ON, other remain blank
 
Upvote 0
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:
Upvote 0
Hi Fluff, the code didn't put an "ON" in Y26 for me.
 
Upvote 0
Are you sure Y26 is blank, no spaces or anything like that?
 
Last edited:
Upvote 0
=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 :oops:
 
Upvote 0
Thany you both.Blindingly quick, apart from a few initial stumbles whilst testing
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top