Data Filtering (Empty Cells)

[Sheharyar]

I have a huge data (approximately 46000 rows). There is a column with the heading "Location". There are total three locations in that column but they are used as headings. For example, first there is "Location 1" and then the next 400 cells are empty which indicate that they are also "Location 1". After that "Location 2" comes in and the next 200 cells are empty which indicate that they are "Location 2" and same is the case with "Location 3". The whole data is arranged in this manner in the column.


What I want to do is to fill up the empty cells as per their "Location". Can i do it one go? It is very frustrating to manually filling up the empty cells considering the huge amount of data.

 

[vasim]

http://chandoo.org/wp/2011/10/17/fill-blank-cells-in-a-table/

 

[NARAYANK991]

Hi Sheharyar ,


Another way is to use a macro.


Public Sub Fill_up_data()

Application.ScreenUpdating = False

Sheets("Sheet1").Select 'Change this Sheet to whichever sheet you are using

Start_Location = Range("A5").Address 'Change A5 to the start address you are using

End_Location = Range("A10000").Address 'Change A10000 as you want.

Last_Row = Range(End_Location).Row()

Range(Start_Location).Activate

Current_Value = ActiveCell.Value

Do While ActiveCell.Row() < Last_Row

Current_Value = ActiveCell.Value

ActiveCell.Offset(1, 0).Activate

Do While ActiveCell.Value = "" And ActiveCell.Row() < Last_Row

ActiveCell.Value = Current_Value

ActiveCell.Offset(1, 0).Activate

Loop

Loop

Application.ScreenUpdating = True

End Sub


You can do the same with a FOR loop , using a counter to go from the start row till the last row.


Narayan

 

[Sheharyar]

Thanks Indian & Narayan. You guys have been very helpful.