Filling In Next X Filtered Rows Automatically With Formula or Value in Excel

Question

I was wondering if there was a method in Excel to fill in the next X number of rows in a blank column if the dataset is filtered. So for example, if I have the following filtered, fictitious data set:

           Color          Price             Brand           Manufacturer

Row 1       Red            2                DumFun   

Row 2       Red            3                DumFun

Row 14      Green          2                DumFun

Row 17      Orange         5                DumFun

Row 23      Purple         4                DumFun

Row 59      Orange         3                DumFun

  .            .           .                   .                 
  .            .           .                   .                 
  .            .           .                   .                 
  .            .           .                   .                 

Row 984     Blue           5                DumFun           

I have filtered the dataset for brand name, in this case "DumFun". What if I wanted to add "Taiwan" automatically for the first 130 rows, and then "Japan" for the next 150 applicable rows in my dataset, etc. without scrolling manually? Moreover, is there a way to ensure that these added values only appear in the next X rows for what's visible on my screen? For example, if I add "Taiwan" to just the first 4 rows, then I could have "Taiwan" in Rows 1, 2, 3, and 4. But 3 and 4 are filtered. In reality I just want to automate adding the values to Rows 1, 2, 14, and 17: what I see on my screen.

Answer 1

1. Un-filter everything
2. Add a column to the far left, label it "index" (I assume Column A)
3. Fill this column with 1 - 984 (or whatever your max is)
4. Filter your results
5. In the manufacture column add the equation =If(A1<=150,"Taiwan",if(and(A1>150,A1<=280),"Japan","Singapore"))
6. Drag this equation down, and it will only fill the values into the visible cells (if you copy and paste, it will fill the values into the hidden cells as well)
7. Un-filter the results, then copy the manufacture column, and paste-special values into the manufacture column (this will overwrite the formulas)

There are plenty of ways to do this in VBA, but this is a quick way that doesn't involve programing.
You could also add the criteria of DumFun into the If equation above, and skip the filtering steps.