Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Filling Blanks In A Column

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi
I need to fill in the blank cells in a column of 10k rec. eg:
ColumnA
123
123
456
789**Blanks should be filled with the last# in the cell above the blank cell
...Blank
...Blank
...Blank
234
234
345

**In this case blanks should be replaced with 789
A macro or a vba code will help

Thanks a lot in advance

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Delete Blank Rows in Excel
- This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This UDF (user defined function) extracts the last word or characters from a cell in Excel. This is done by finding the
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back

Similar Topics







Hi All! Great forum you have here and the resources available here via search are great. I haven't found what I'm looking for - so I'm hoping you all can assist with something I think is quite simple .. but I keep hitting a wall.

I have a situation in Excel that currently looks like this:

Column A
a
<blank>
<blank>
b
<blank>
<blank>
<blank>
<blank>
c
<blank>
<blank>
<blank>
<blank>
<blank>
<blank>
d
(^^ each line represents a row ^^)

This goes on for several thousand lines - sometimes there are no blanks, sometimes 1 and sometimes 30 or more.

What I'd like to do is find a formula I can use on the column to the left (or right) of the data above to create a consistent list without blanks as seen he

Column A
a
a
a
b
b
b
b
b
c
c
c
c
c
c
c
d
(^^ each line represents a row ^^)

Essentially, I want to fill in the blanks after each value with the value from above - when I reach a new value I want to fill in the blanks after the new value with the data from the new value.

I hope I've explained this well enough. Thank you in advance for your help!

-E


I have a sheet with the following data in column A beginning from row 2.

Mon
blank
blank
blank
blank
blank
Tue
blank
blank
Wed
blank
blank
blank
blank
Thu
blank
blank
Fri
blank
blank
blank
Sat
blank

What I want to do is fill in the blank cells with the same day as the bold day above it using a formula that allows for the days and blanks to be in different rows as the above data is linked to another sheet.

How could I achieve the above?

Thank you in advance,
suprsnipes


Hello everyone,

Here is my problem. I have a spreadsheet with four columns, which are variables nested within each other. What I am currently doing is going through a range, finding the blank cells, filling all the blanks with the value of the non-blank cell above it, and filling these blanks until I reach a non-blank cell. Simply put, all I am doing is clicking on the cell's handle and filling the blanks (it only fills the cells down until it finds a cell with a value).

I am tediously going through each range of cells, autofilling down, and working from right to left.

There must be an easier way where I can select the range of cells, run a macro, and it does the autofill for me.

Any suggestions?

Thanks in advance,

Brock


I have a Sheet that has Column sequence as follows

Blue
blank
blank

Red
blank
blank
blank
Yellow
blank




I am looking to datasort to group all Blanks - then add a formula; that whn resorted to above layout, the formula populates the code as above - i.e both blanks below Blue will state Blue. Some codes have 2 or 3 blanks whilst others have 50 or 60.

.......or is there a better approach?


All--I am working with an excel spreadsheet with several hundred rows and 60 columns. The column of the first filled cell in each row varies. For example, row 1 may have blank cells until column 5, row 2 until column 15, row three until column 3, row four until column 7 etc.

I need to fill all blank cells on the left of the worksheet (from first to last row) with the number 1. I could simply fill all blanks except there are blank cells on the right side of the worksheet that need to stay blank. row 1 may end with column 14 (15 thru 60 are blank), while row 2 goes to 30, and row 3 goes to 45, etc.... The empty cells on the right side need to stay that way.

My thoughts so far are to write a macro to count the number of empty blanks in each row with a Match statement...then somehow fill in those blanks using the result of the statement in each row...working my way down the spreadsheet. Any help or ideas that you can provide would be appreciated. I have worked a great deal with functions in excel but have a lot less experience with macros.

kim


Macro to Fill Blank Cell with Values above.

--------------------------------------------------------------------------------

I am using the following procedure to fill in blank cells with the value above:

Select the column with the blanks.
Goto Edit>Goto...Special and select blanks.
Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
Select the column, copy and paste special values

However this does not work with what I need to do now.

What I need to do is this:

12345
45678


9658
4238
8210
4563




This means I need to fill in the blank cells with the SERIES OF VALUES immediately above the blanks.

The reason I cant use the above procedure is that the size of the series varies.

For example i will have two values followed by two blanks then 5 values followed by five Blanks.


Any help is appreciated

Thanks


Hi! to Everyone

There are many Software and Addin and Macro or Code to Delete or Eliminate the Duplicate Data

But, I want to Delete The Duplicate BLANKS Row.I means to Say that Delete Every Repeated Blank row

Example

Hardeep
Blank
Renu
Blank
Blank
ABc
Blank
DEF
Blank
Blank
Blank
Now i Want in this format

Hardeep
Blank
Renu
Blank
ABC
Blank
DEF
Blank
and so on,


Thanks in Advance


I have a column in a spread sheet which has blanks cells in it. I wish to run a macro that fills in these blank cells with the data that is in the nearest cell above it, which is not blank.

Have I made sense? Any help much appreciated.

john


i have a 5 by 10 table (5 columns 10 rows)
values only equal 1 or blank
i want to re-order this table in another identical table so that no row will have more than one value (each row will have a maximum of one "1")

currently, the table looks something like this:

blank blank blank 1 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank
blank blank blank blank blank

what i would like the idential table to show is:

blank blank blank 1 blank
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank


does that make sense? (similar logic with more 1s through-out)


Hello all,
I need help to count the following.

- col A --- col B
-- x ------ blank
-- x ------ blank
-- x ------ data
-- y ------ data
-- y ------ data
-- y ------ data
-- y ------ blank
-- z ------ data
-- z ------ blank
-- z ------ blank
-- z ------ blank
-- z ------ data

Can someone please supply me with the formula that will count the amount of blanks and filled cells for each value in column A.

I need to create the following table

x blank = 2
x filled = 1

y blank =1
y filled =3

z blank =3
z filled = 2

Thank you for your help in advance.


Hi,

Have a small problem that I think I can overcome with excel functions. I have a nasty little dataset that looks like:

Date1 Date2 Date3 Date4
A0 14 13.5 14 13
A1 12 12 12.5 11.5
A2 11 Blank Blank 13
A3 14 Blank 11 Blank
A4 Blank 16 27 12
A5 Blank Blank 3 Blank
A6 10 7 16 9

Where blanks can appear on any row in any column (and there are usually 10-15 blanks between datapoints. I cannot sort the data in anyway as I need the 'A' column to be sequential. I need to subtract two consecutive values (working down) and divide them by the difference of there respective 'A' column values. Example would be [(Date1:A0 - Date1:A1)/(A0-A1)] and so on down to [(Date1:A3 - Date1:A6)/(A3-A6)].

I'm not worried about the last datapoint so it can run into error down the column. I see four elements, selecting the first cell (easy enough), selecting the next non-blank value and 'matching' both values to there column A values (simple with a match or lookup).

Any ideas? Thanks for giving this a look.


First off, let me tell you I am not a programmer so I will not know how to use code to do what I am trying to do. I would like to do the following using the inherent formulas that can be used in Excel. Here goes. I have text (people's names) in various cells in column A of a worksheet, with blank cells in between (these names are being populated by a link from another sheet). There may be 30 blank cells between one name and the next or there may be just one blank cell. I would like Excel to fill in Column A on another worksheet, one after the other showing no blank cells, by looking at the original worksheet, Column A, and if a name is in the cell, it starts filling in Column A on the other worksheet with each of the names it finds, so that I end up with a column of names from the original worksheet with no blanks. Is there a way to write a formula for Excel to do this, or would I have to know how to code something like this in VB to accomplish it?

Thank you,

Toni

Hi All,

I have named ranges which have blanks at both start and end. So for eg:

<Blank>
<Blank>
<Blank>
3
4
57788
9.23
<Blank>
<Blank>

Now I know how to isolate the data from a named range when there are no blanks at the end using

=offset(namedrange, countblank(namedrange),0,count(namedrange))

but am not sure how to do this when blanks exist at the end also. Any advise on how to achieve this will be very helpful.

Thanks


Hi again,

I've got a list like this:

45
32
23
blank
34
45
blank
blank
blank

any idea how to count the number of blanks after the final non-blank cell?


Okay, this one is driving me crazy. I have a macro that creates a pivot table, then copies it to another spreadsheet by doing a "Paste Special - Values", followed by a "Paste Special-Formats". My problem is that column A contains text values instead of numbers, and blanks which I want to fill in. When I select that column and use the routine Edit>Go To>Special>Blanks, then hit the " = "sign, then up arrow, then ctrl-shift-enter, the blanks fill in on only to the next text number, not all the way down the column. I know the problem there is that the numbers need to be numbers, but I don't know how to programmatically do that.

If I first select the column and multiply it by 1, the blanks are replaced with zeros, and again my routine won't work to fill in the blanks, 'cause they're no longer blank.

If I insert a column and use a formula such as Code:

=IF(B3<>0,B3,"")


and then copy that column and paste the values back into column B, it looks like everything is right, except I still need to fill in the blanks. However, going to Edit>GoTo>Special>Blanks tells me there are no blanks.

I've attached my spreadsheet so you can see what data I'm working with. I realize this must be simple, but it's simply beyond me right now. Thanks in advance for any help I can get on this.


I am trying to populate a spreadsheet that has many blanks in one column. I want to use the text in the cell above the blank until the next filled in cell is reached. Then I want to use the text from that cell. I have tried using these instructions, but what is returned is the cell location from above the first blank repeated in each blank cell. I'm trying to fill with text and get the text to change as the already-filled cells change:

1. Select column A.
2. Choose the Edit | Go To... | Special... menu
command, select "Blanks" and press [ OK ].
3. Type the formula, =A2, and press Control+Enter.
4. Select column A once again.
5. Choose the Edit | Copy followed by
Edit | Paste Special... Values.

For step 3, I've tried using the cell name, the up arrow to point to the cell, and moving my cursor to the cell. Nothing has worked. Attached is a snip of what I am getting (note that the rows start with 200, as the first digit has been truncated.)

I'd appreciate any suggestions.

Sare


Hi, I want to auto fill data (by clicking the small square button that forms when we click a cell) for multiple columns. Is there a way to select all the columns and autofill at one go? Else clicking one data after another is extremely laborious.

Also how we can retain the same auto-fill format for copying all the cells (like 'Copy Cells', 'Fill Series' etc. that we have to choose especially for auto-filling dates).

Reference data -(column wise data; 'BLANK' to be considered as empty cells)

Sl. Month Person Product Quantity
1 Jul-10 Amish Apple 10
2 BLANK BLANK BLANK BLANK
3 BLANK BLANK BLANK BLANK
4 BLANK BLANK Banana 11
5 BLANK BLANK BLANK BLANK
6 BLANK Bobby Apple 9
7 BLANK BLANK BLANK BLANK
8 BLANK BLANK BLANK BLANK
9 Aug-10 Amish Banana 12
10 BLANK BLANK BLANK BLANK
11 BLANK Bobby Apple 8
12 BLANK BLANK BLANK BLANK
13 Sep-10 Amish Apple 10
14 BLANK BLANK BLANK BLANK
15 BLANK BLANK BLANK BLANK


Hi,
I hope someone can help, I need to automate a conditional fill down in excel...

I have a sheet, some of the columns have rows of empty cells (the amount of empty cells varies). Whenever there is an empty cell I need to fill down, however when a new value is encountered that must then be filled down and so on.

This bit I have as:
VB:

Set rngWBSv1 = Range("c2", Range("c65536").End(xlUp)) 
For Each clWBSv1 In rngWBSv1 
    If clWBSv1.Value = "" Then clWBSv1.Value = clWBSv1.Offset(-1, 0).Value 
Next clWBSv1 
 
Set rngWBS2 = Range("d2", Range("d65536").End(xlUp)) 
For Each clWBS2 In rngWBS2 
    If clWBS2.Value = "" Then clWBS2.Value = clWBS2.Offset(-1, 0).Value 
Next clWBS2 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Which works fine (I've duplicated it for all the columns I need).

However the final part is that I need to test against a certain phrase in Column A, same row as to be filled..
If column A contains phrase AND target column does not have a NEW value then there must be no fill down (or fill down blanks), until a new value in target column is reached.

I need this conditional fill down in columns B to H (but not F).

To illustrate my point please find an excel sheet attached (only 21 rows!).

From the attached the end result I'm after is (the row count is from the phrase "Sub-total per project" in column A):
(Rows 2 to 6)
B3 filled to B6
C3 filled to C6
D3 filled to D6 (which will fill down a blank)
E3 filled to E6 (which will fill down a blank)
G3 filled to G6
H3 filled to H6

(Rows 7 to 9)
B7 filled to B9
C7 filled to C9
D7 filled to D9
E7 filled to E9
G7 filled to G9
H7 filled to H9

(Rows 10 to 12)
B10 filled to B12 (which will fill down a blank)
C10 filled to C12 (which will fill down a blank)
D10 filled to D12 (which will fill down a blank)
E10 filled to E12 (which will fill down a blank)
G10 filled to G12
H10 filled to H12

(Rows 13 to 17)
B13 filled to B17 (which will fill down a blank)
c13 filled to C17 (which will fill down a blank)
D13 filled to D17
E13 filled to E17
G13 filled to G17
H13 filled to H17

(Rows 18 to 21)
B18 filled to B21
C18 filled to C21
D18 filled to D21 (which will fill down a blank)
E18 filled to E21 (which will fill down a blank)
G18 filled to G21
H18 filled to H21

Thanks in advance to any help!

Alvin

Hello everyone.
I'm trying to select all blank cells (Excel 2010) in a column. I've run a macro that gathers data from a report, but when certain criteria are met the row is left blank (using =if(this, then, ""). I've then copy/paste special/values-d the 5 columns of gathered data to a new worksheet.

Now, I want to delete all rows that have a blank cell in column A. I highlighted column A, clicked Home>Find & Select>Go To Special and selected Blanks.

But it doesn't find anything.

I thought maybe there was just a space in those cells, but when I go to Find, and try to search for blanks (I tried searching for both " " and * *), and, in find options, selected 'match entire cell contents', it finds nothing.

If I select some of the blank cells and hit Delete, then the Go To Special / Blanks finds those cells.

Any ideas?


Hi there all --
Suppose I have column A with 20 rows. In A1 I have text, say "Bob" but in cells A2-A4 it is blank, then in cell A5 is "Ted" but cells A6-8 it is blank.

It continues like this all the way down with blanks between the next unique entry. I want to populate the blanks between with the unique entry from above the blanks, so A1-A4 would all say "Bob" & A5-A8 would say "Ted" & so on. -- Yes I could drag down, but in reality I have 5000 rows like this so a formula that did this work for me would be helpful.

Any ideas? Thanks a head of time.


I have an instance where I have a list of numbers in different rows with different amounts and I want to add up the last 6 numbers, they're all in the same column however they're not all in consecutive rows.

Also, the gaps between the rows is not consistent.

The example below explains what I mean (I've add numbers in between so you can see the varying gaps, but they're actually blank cells)

Quote:

(B1)ADD ME = 2
(B2)**blank cell**
(B3)**blank cell**
(B4)**blank cell**
(B5)**blank cell**
(B6)ADD ME = 5
(B7)**blank cell**
(B8)**blank cell**
(B9)**blank cell**
(B10)**blank cell**
(B11)**blank cell**
(B12)**blank cell**
(B13)**blank cell**
(B14)**blank cell**
(B15)**blank cell**
(B16)**blank cell**
(B17)**blank cell**
(B18)ADD ME = 2
(B19)**blank cell**
(B20)**blank cell**
(B21)**blank cell**
(B22)**blank cell**
(B23)**blank cell**
(B24)**blank cell**
(B25)**blank cell**
(B26)**blank cell**
(B27)**blank cell**
(B28)**blank cell**
(B29)**blank cell**
(B30)**blank cell**
(B31)**blank cell**
(B32)ADD ME = 5
(B33)ADD ME = 3
(B34)**blank cell**
(B35)**blank cell**
(B36)**blank cell**
(B37)**blank cell**
(B38)**blank cell**
(B39)**blank cell**
(B40)**blank cell**
(B41)**blank cell**
(B42)**blank cell**
(B43)**blank cell**
(B44)**blank cell**
(B45)**blank cell**
(B46)ADD ME = 0
(B47)**blank cell**
(B48)**blank cell**
(B49)**blank cell**
(B50)**blank cell**
(B51)**blank cell**
(B52)**blank cell**
(B53)**blank cell**
(B54)**blank cell**
(B55)**blank cell**
(B56)**blank cell**
(B57)**blank cell**
(B58)**blank cell**
(B59)**blank cell**
(B60)ADD ME = 1
(B61)**blank cell**
(B62)**blank cell**
(B63)**blank cell**
(B64)**blank cell**
(B65)**blank cell**
(B66)**blank cell**
(B67)ADD ME = 5

What code can I add to say "add up the last 6 numbers containing values"?


Hello,

I just can not seem to get this right. I'm on Excel 2003 and have a macro to fill in blank cells with the value from the cell above it. Now I need a macro to undo this. There may be 1 or 2 cells or as many as 12 cells in sequence that were filled with the same value (ie: B12,B13,B14,B15,B16, are filled from B11 value, B18,B19 are filled from B17 value, B21,B22,B23 are filled from B20 value, and so on) . I need to leave the top/beginning value and return the filled cells to blanks.

If I just reverse the fill macro it skips everyother cell because it "blanks" the first duplicate and then when it moves down to next cell (which reads equal to the "fill" cell source which is now 2 rows up) but the macro is comparing to the cell it just set to blank.

Here is original "fill" macro. Please help me figure out the correct undo macro that will leave only the top/beginning value in each random group.

Thanks a bunch... here is macro...

Sub FillCellfromAboveCell()

For Each Cell In Range("B9:B200")
If Cell.Value = "Stop" Then Exit Sub
If UCase(Cell.Value) = "" Then Cell.Value = Cell.Offset(-1, 0)
Next Cell
End Sub


Hi there, I need help with a macro I have.
I have data in one column. After some of the entries there are blank cells:
12345
blank
23456
blank
blank
34567
67867
blank
I need to be able to copy the cell above the blank in. The number of blanks is not consistent and sometimes there are multiple numbered cells.
I recorded a macro that goes to the first cell eg 12345 and copies the data
into the blank cell.
12345
12345
23456
But my problem is when there is a cell after this that is not blanks.
Because the macro was record and playback using keyboard and mouse,
I cant figure out how to only select if
the next cell is a blank and not copy into a cell that has a number
Like the third last cell.
34567
67867
The macro takes 34567 and pastes into 67867

Any help is greatly appreciated as I have about 2000 cells in one column
and have to do this manually so its a huge time waster.


I have a spreadsheet with over 6,000 records. it is setup as follows:

A1:E1 are the column headings
A1= CustomerID; B1= Customer; C1=Item; D1= Item Description; E1= Price


Rows 1-5 have Customer ID & Customer informaion
Rows 6-15 is the order information for Customer in Row 5 but the column information for column A6:B15 are blank

I want to automatically fill in the values in row A6:B15 with the first non-blank information from above. In this case it would be A5:B5.

This needs to be done by finding the blank cells since there is no pattern to the information and there are too many blanks to go through and manually edit the information and some blanks are only one row and others are multiple rows.

Thanks for the help!
Steve M.


Excel friends:

I have many columns filled in with blanks first and numbers after, the first column on the lest is an index, 1 to 250, my question, How would be the formula at the top of each column to identify the index number of the first non blank cell.

For example, if the 5th column has 50 blanks and after them 3 numbers, and after that 197 blanks. I would like to have a formula on the top of the 5th column that results in 51, the first non blank cell on the 5th column. The goal is copying the formula to all other columns, and automatically identify the first blank cell index in each column.

Thanks for your help!

actjfc