VBA to Fill all empty cells in all columns in a range

What is the VBA code to:

1) calculate then select the complete "used range" (all rows & columns) in a 		
	Worksheet
2) fill-down all empty cells with the last value preceding the empty cell, without	
	overwriting the data already there.


	Original		After Fill

1	11111		11111
2	22222		22222
3			22222
4			22222
5	12345		12345
6	67789		67789
7	78678		78678
8			78678
9	99999		99999

Using Excel 2003

TIA Dennis

ps.  I have a separate question next post for an entirely different subject
-1
2/29/2004 11:15:32 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1078 Views

Similar Articles

[PageSpeed] 57

There's a code sample here, for fill blank cells in a column:

   http://www.contextures.com/xlDataEntry02.html

Dennis wrote:
> What is the VBA code to:
> 
> 1) calculate then select the complete "used range" (all rows & columns) in a 		
> 	Worksheet
> 2) fill-down all empty cells with the last value preceding the empty cell, without	
> 	overwriting the data already there.
> 
> 
> 	Original		After Fill
> 
> 1	11111		11111
> 2	22222		22222
> 3			22222
> 4			22222
> 5	12345		12345
> 6	67789		67789
> 7	78678		78678
> 8			78678
> 9	99999		99999
> 
> Using Excel 2003
> 
> TIA Dennis
> 
> ps.  I have a separate question next post for an entirely different subject


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
2/29/2004 11:37:57 PM
So what happens if you don't have values in every cell of the top row of your
usedrange, as you will get 0s and references to cells at the bottom of the
sheet.  If that is not an issue however, then try this:-

Sub FillBlanks()

Dim LCol As Long
Dim ColNum As Long
Dim Rng As Range
Dim Cel As Range

Application.ScreenUpdating = False

LCol = ActiveSheet.UsedRange.Column - 1 + _
        ActiveSheet.UsedRange.Columns.Count

For ColNum = 1 To LCol
     On Error Resume Next
     Set Rng = Intersect(ActiveSheet.UsedRange, Columns(ColNum), _
         Cells.SpecialCells(xlCellTypeBlanks))
     For Each Cel In Rng
        Cel.FormulaR1C1 = "=R[-1]C"
     Next Cel
Next ColNum
Application.ScreenUpdating = True

End Sub


This leaves the formulas in there though.  If you want the data hardcoded, then
amend to:-

Sub FillBlanks()

Dim LCol As Long
Dim ColNum As Long
Dim Rng As Range
Dim Cel As Range

Application.ScreenUpdating = False

LCol = ActiveSheet.UsedRange.Column - 1 + _
        ActiveSheet.UsedRange.Columns.Count

For ColNum = 1 To LCol
     On Error Resume Next
     Set Rng = Intersect(ActiveSheet.UsedRange, Columns(ColNum), _
         Cells.SpecialCells(xlCellTypeBlanks))
     For Each Cel In Rng
        Cel.FormulaR1C1 = "=R[-1]C"
     Next Cel

     With Columns(ColNum)
         .Copy
         .PasteSpecial Paste:=xlValues
     End With

Next ColNum
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


If you only want this done for cells between the first and last entry in each
column, then try this:-

Sub FillBlanks()

Dim LCol As Long
Dim ColNum As Long
Dim Rng As Range
Dim Cel As Range

Application.ScreenUpdating = False

LCol = ActiveSheet.UsedRange.Column - 1 + _
        ActiveSheet.UsedRange.Columns.Count

For ColNum = 1 To LCol
     On Error Resume Next
     FrstRow = ActiveSheet.Cells(1, ColNum).End(xlDown).Row
     LastRow = ActiveSheet.Cells(Rows.Count, ColNum).End(xlUp).Row
     Set ColRng = Range(Cells(FrstRow, ColNum), Cells(LastRow, ColNum))

     Set Rng = Intersect(ColRng, Columns(ColNum), _
         Cells.SpecialCells(xlCellTypeBlanks))
     For Each Cel In Rng
        Cel.FormulaR1C1 = "=R[-1]C"
     Next Cel

     With Columns(ColNum)
         .Copy
         .PasteSpecial Paste:=xlValues
     End With

Next ColNum
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Dennis" <dmburgess@removespamameritech.net> wrote in message
news:kmr4405mm36bof5haqb8qip5a7vv0heqko@4ax.com...
> What is the VBA code to:
>
> 1) calculate then select the complete "used range" (all rows & columns) in a
> Worksheet
> 2) fill-down all empty cells with the last value preceding the empty cell,
without
> overwriting the data already there.
>
>
> Original After Fill
>
> 1 11111 11111
> 2 22222 22222
> 3 22222
> 4 22222
> 5 12345 12345
> 6 67789 67789
> 7 78678 78678
> 8 78678
> 9 99999 99999
>
> Using Excel 2003
>
> TIA Dennis
>
> ps.  I have a separate question next post for an entirely different subject


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004


0
ken.wright (2489)
2/29/2004 11:56:06 PM
Thanks Debra & Ken for your knowledge & time!  Dennis


"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote:

>So what happens if you don't have values in every cell of the top row of your
>usedrange, as you will get 0s and references to cells at the bottom of the
>sheet.  If that is not an issue however, then try this:-
>
>Sub FillBlanks()
>
>Dim LCol As Long
>Dim ColNum As Long
>Dim Rng As Range
>Dim Cel As Range
>
>Application.ScreenUpdating = False
>
>LCol = ActiveSheet.UsedRange.Column - 1 + _
>        ActiveSheet.UsedRange.Columns.Count
>
>For ColNum = 1 To LCol
>     On Error Resume Next
>     Set Rng = Intersect(ActiveSheet.UsedRange, Columns(ColNum), _
>         Cells.SpecialCells(xlCellTypeBlanks))
>     For Each Cel In Rng
>        Cel.FormulaR1C1 = "=R[-1]C"
>     Next Cel
>Next ColNum
>Application.ScreenUpdating = True
>
>End Sub
>
>
>This leaves the formulas in there though.  If you want the data hardcoded, then
>amend to:-
>
>Sub FillBlanks()
>
>Dim LCol As Long
>Dim ColNum As Long
>Dim Rng As Range
>Dim Cel As Range
>
>Application.ScreenUpdating = False
>
>LCol = ActiveSheet.UsedRange.Column - 1 + _
>        ActiveSheet.UsedRange.Columns.Count
>
>For ColNum = 1 To LCol
>     On Error Resume Next
>     Set Rng = Intersect(ActiveSheet.UsedRange, Columns(ColNum), _
>         Cells.SpecialCells(xlCellTypeBlanks))
>     For Each Cel In Rng
>        Cel.FormulaR1C1 = "=R[-1]C"
>     Next Cel
>
>     With Columns(ColNum)
>         .Copy
>         .PasteSpecial Paste:=xlValues
>     End With
>
>Next ColNum
>Range("A1").Select
>Application.CutCopyMode = False
>Application.ScreenUpdating = True
>
>End Sub
>
>
>If you only want this done for cells between the first and last entry in each
>column, then try this:-
>
>Sub FillBlanks()
>
>Dim LCol As Long
>Dim ColNum As Long
>Dim Rng As Range
>Dim Cel As Range
>
>Application.ScreenUpdating = False
>
>LCol = ActiveSheet.UsedRange.Column - 1 + _
>        ActiveSheet.UsedRange.Columns.Count
>
>For ColNum = 1 To LCol
>     On Error Resume Next
>     FrstRow = ActiveSheet.Cells(1, ColNum).End(xlDown).Row
>     LastRow = ActiveSheet.Cells(Rows.Count, ColNum).End(xlUp).Row
>     Set ColRng = Range(Cells(FrstRow, ColNum), Cells(LastRow, ColNum))
>
>     Set Rng = Intersect(ColRng, Columns(ColNum), _
>         Cells.SpecialCells(xlCellTypeBlanks))
>     For Each Cel In Rng
>        Cel.FormulaR1C1 = "=R[-1]C"
>     Next Cel
>
>     With Columns(ColNum)
>         .Copy
>         .PasteSpecial Paste:=xlValues
>     End With
>
>Next ColNum
>Range("A1").Select
>Application.CutCopyMode = False
>Application.ScreenUpdating = True
>
>End Sub

0
3/1/2004 12:16:11 AM
Sub test3001()
     Dim rng As Range, iCell As Range
     Set rng = _
     	Worksheets("Sheet4").UsedRange.SpecialCells(xlCellTypeBlanks)
     For Each iCell In rng
         iCell.Value = iCell.Offset(-1, 0).Value
     Next
End Sub

Alan Beban

Dennis wrote:
> What is the VBA code to:
> 
> 1) calculate then select the complete "used range" (all rows & columns) in a 		
> 	Worksheet
> 2) fill-down all empty cells with the last value preceding the empty cell, without	
> 	overwriting the data already there.
> 
> 
> 	Original		After Fill
> 
> 1	11111		11111
> 2	22222		22222
> 3			22222
> 4			22222
> 5	12345		12345
> 6	67789		67789
> 7	78678		78678
> 8			78678
> 9	99999		99999
> 
> Using Excel 2003
> 
> TIA Dennis
> 
> ps.  I have a separate question next post for an entirely different subject

0
3/1/2004 5:46:18 AM
Reply:

Similar Artilces:

Removing characters from cell
Hope someone can help me with this one.... I have text in column "A" a list of parts like the following..... ...nhg1234 ..nhg1235.54 nhg3456 ....nhg1253.7 Is thier anyway of removing the dots in front the part number? I dont want the points moving after the the main part of the number. Please help TiA mag()() Do you part numbers all start with nhg? If yes, you could select column A edit|Replace what: .n with: n replace all And keep hitting the replace all button until all are fixed. "Mag()()" wrote: > > Hope someone can help me with this one.... >...

How to count cells
Hi, I want to put a summary of Job orders in a table. (Excel (Zip) file attached) I am having a problem of total. I want to make a total of only approved job orders not the all job orders. Also if the column contains hot / cold type, I need red and bold for hot job order of complete row. Also I want to be counted by job type and entity. For example, in the total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how can i display in the abstract table. Can anyone help me please. +-------------------------------------------------------------------+ |Filename: count.zip ...

Area with id "VM" has an empty title.
Just started getting this error when I try to login and I cannot find where the error is coming from. No changes have been made that I am aware of. Any help would be greatly appreciated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: Microsoft.Crm.Errors.CRMException: Area with id "VM" has an empty title. Source Error: An unhandled exception was generated during the execution of the current web reque...

Unpopulated Cell Address
Hi I'm a newbie and was wondering can anyone help with my query. I am trying to find out how to print a spreadsheet with just the data that is contained in certain cell address's. Where a cell address is not populated I want the whole row to not appear in a printed copy. Any ideas? Try this idea Sub hiderowsandprint() With Range("a1:a21") .SpecialCells(xlCellTypeBlanks).Rows.Hidden = True .Resize(21, 8).PrintPreview .Rows.Hidden = False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Mick Smith" <notsa...

change the formula by changing contents of cell
I have a table that ranks a list of players by their statistics, for example, when I change cell B2 to "walks", my formula (large) lists the player with the most walks in cell B4, 2nd most in B5, 3rd in B6... that works well because more walks are good. But, when I change cell B2 to "strikeouts" I want it to list the player with the fewest number of strikeouts in B4, next fewest in B5 and so on. Is there a way to change the "large" function to the "small" function within the formula? I have set up a helper cell (C2) that changes fro...

Control can't be edited; it's bound to a replication system column 'TableName'.
I have an Access 2007 accdb database. It hast a table in it with field names TableName and FieldName and a bunch of others. This table has had a small number of records added to it (37) over the life of the database. Suddenly, the two fields TableName and FieldName cannot be edited. That is even if the table is open as a table and you try to type a new record. When you do that an error message is briefly displayed in the left hand end of the Access main window's status bar. For example if you try to input anything into the TableName field you will be blocked and you will briefl...

Preventing Column Cell Values from Printing
I have a summary worksheet with several columns of information. There is one particular column (E25:F39) where I don't always want to print the values. Is there some simple way to toggle this column's values on and off just before hitting the print command? I was thinking of using an adjacent tick box outside the print area, which when ticked would print the column values, and vice-versa. I'm looking for a formula that would examine the tick box, and if the tick (letter 'a' in Marlett font) is not present (i.e. cell value = ""), then the values in column (E25:...

How do I force all capital in a column of lower case names?
I read the help, but it doesn't tell me where to enter the formula. If you do not have a blank column next to the mentioned column, then insert one. Enter the formula in a cell adjacent to the cell you want to change, and copy down. Then copy this column, go to the original column, select a cel, right click and click on Paste Special, tick values. Delete the helper column. Iow, with you lower case data in Column E, click on Column F, if not empty, and insert a column. F is then an empty column. Say your data starts in E2, then in F2 enter =UPPER(E2). Move cursor to the botto...

How to find a value with multi-column, multi-record list
I have a spreadsheet with $costs arranged according to values in both rows and columns. How do I create drop down lists for both the rows and columns and then return the cell value? eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each cell within this group how do I create drop downs for each selection, 2-6 and B-G, and return the value to a selected cell. In a typical table of this nature there would be descriptive row and column headers. To do a lookup on this table you would then look for the intersection of a specific row header and a specific column ...

How do I change the value in cell based on a future date
I would like certain cells to be cleared after a certain date one way: Put this in the ThisWorkbook code module (Right-click the workbook title bar and choose View Code): Public Sub Workbook_Open() If Date > #12/15/2004# Then _ Sheets("Sheet1").Range("A1,B2,J10,K39").ClearContents End Sub Note that this won't work if the user opens your workbook with macros disabled. Note also that there's no way to prevent a user from accessing your data - i.e., they can set their clock back and open the file. You can make things more difficu...

IF COUNTIF & COUNTA on Filtered Visible Cells #7
Hi Frank, Hope this will help. Correction to Last Posting: My Helper Column "U" increments one Row at a time and says: Check from Row above Current Row back to beginning of my "V" Range: i the Room was used previously give me the Last (MAX) time it was used b returning the relevant Row Number of the (text based) Group Nam (Executive, Trainees, Manager, Graphics, etc.) that used the Room Last The Helper Column "U" then passes this data to the Formula in Colum "T" - it checks for the criteria within the specified Range and doe the COUNT calculation us...

Column Width Behavior
I use pivot tables alot - and I am working with a pivot table where I've set the column width to 9. When I pivot new information in, the column width automatically adjusts to fit, then I have to go and reset the columns back to 9. Is there anyway to override this "adust to fit" behavior and just let the pivot table fit as much text as it can into the column width I specify? Kirk, Unselect the 'Autoformat Table' option in the Options section of the Pivot Table. HTH, Gary Brown "Kirk P." wrote: > I use pivot tables alot - and I am working with a pi...

Excel 2000
Hi! *First off:* I have created a spreadsheet that has a dynamic range an data validation. *Next:* i know i can add options to the 'dynamic range' and my dro down menu will add the option to its list. *Problem:* How can I make it so the user can add data into th validated data list/drop down menu rather than having the user addin it into the dynamic range? *Example:*this drop down menu indicates how often a system is updated weekly, monthly, etc. etc... i don't want to restrict it, so i want t make it so they can indicate their own time on how often those update occur. TIA -...

"Changed by" Column is empty
Outlook 2003/Exchange 2000 Hi, in my mailbox, the "Changed by" column remains empty throughout all folders. When I change to my Team Mailbox, the "Changed by" column has the desired information. Even when I move a mail from the Team Mailbox to my mailbox, the "Changed by" column remains empty. Is this a bug or I am doing something wrong. Thank you very much. Christoph ...

Data within a cell
If names are in cells as "last name, first name, middle initial" can a macro be run to change the order to "first name, middle initial, last name"? A formula will do Assuming you have spaces between commas and names, =MID(A2,FIND(",",A2)+2,FIND(",",A2,FIND(",",A2)))&RIGHT(A2,FIND(",",A2,FIND(",",A2)))&", "&LEFT(A2,FIND(",",A2)-1) Then just copy down. If you want this more permanent, copy the cells with formulas, right click, paste special. -- Best Regards, Luke M *Remember to click &q...

Transfering a changing cell value progressively through a workbook
I am creating a payment application form in excel 2007 using windows 7. Each worksheet represents one months invoice. Say I have a formula in "sheet 1/ cell Q7" that sums the total billed to date for a particular budget line item. This value will be transfered to a the next months payment application "sheet 2/ cell K7" this becomes the total amount of previous applications, then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, and so on and so on, until the completion of the job. How do I acomplish this. -- Thanks Mike ...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

Trapping empty or null value
I have a subform with a textbox txtAmount bound to field Amount. On the subform's footer is txtTotal with control source =Sum([amount]). When there is no data in the subform I see #Error in the txtTotal. How can I change =Sum([amount]) to something that will test for this condition and display 0 when there is no data in the subform instead?. Robert Unless you are using Access 2007, you can check the RecordCount of the RecordsetClone of the subform. Unfortunately, the new version errors with that, so you have to use a function call. Public Function FormHasNoRecords(frm As F...

Help! page range prob in print
Hi I worked on print.Everythings working perfectly except one problem.ie. When i set the PageRange option to 'ALL' option and press print button..instead of displaying only the pages which has information ..its displaying that all pages from 1 to 65535 r going to be printed. How do i remove this problem? thanks, vani I thing you may failed to set the maximum number of pages to print. Check CPrintInfo::SetMaxPage(nMaxPage); method regds Jibesh -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspecto...

SQLDataAdapter Fill with startRecord parameter
Hi. I can't unserstand how startRecord works; i think there is a problem. I'm using maxrecord=5. If i put zero 0, the dataset is filled with all values in table (rows.count is = @@rowcount) If i put 1 the dataset is filled only with 5 values, but row is not the first one in the table, is the second! If i put 2 the dataset is filled only with 5 values, but row is not the first one in the table, is the third! .... and so on. So MSDN says that startRecord is 0-based index... that seems to be true but in this way we lost our first record !! ...

Switch format characteristics cell-by-cell?
I want to do something like this: IIf([table_a].addr1 <> [tableb].addr1, SwitchToBold([tablea].addr1), [tablea].addr1) Is there such a function or set of functions, e.g. switch to italics or change the font entirely? I can't find it and am not entirely sure where to look. Thanks in advance. On Thu, 30 Aug 2007 11:30:14 -0700, Jonathan Ball <notgenx32@yahoo.com> wrote: >I want to do something like this: > >IIf([table_a].addr1 <> [tableb].addr1, SwitchToBold([tablea].addr1), >[tablea].addr1) > >Is there such a function or set of functions, e.g. swit...

How many characters per cell
How many characters can you fit into one cell. We are having a problem with text being cut off when printing, but i shows up when viewing on either Print Preview or normal view -- aletoconstc ----------------------------------------------------------------------- aletoconstco's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3533 View this thread: http://www.excelforum.com/showthread.php?threadid=55334 Excel Help on "limits" or "specifications" reveals that Excel will allow 32,767 characters to be entered in a cell. However, it goes on t...

Starting a macro by editing a cell
I would like a macro to be initiated whenever someone edits a particula cell, does anyone now how this can be done? Many Thanks, Yianni -- Message posted from http://www.ExcelForum.com Yiannis You would need on change macro in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$A$1" Then my code gets actioned here if target.value = "" then exit sub end if End If End Su -- Message posted from http://www.ExcelForum.com ...

Find Last cell with Date
I need to find the last cell in a row that has a date entered. The date should then be entered into another cell which is updated as more dates are added to the row. Thanks for your help. Ken Russell Remove hat to reply by e-mail kenrussellhat@optushome.com.au --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.742 / Virus Database: 495 - Release Date: 19/08/2004 Hi for column A try: =LOOKUP(9.99999999999999E307,A:A) Note: - returns the last NUMERIC value. that is you can't differentiate between numbers and dates (as ...

Basic Fill Handle
Hi, here's a plain example of what I'm trying to do: In cell B1 is the formula: =A1 In cell B2 is the formula: =A1 In cell B3 is the formula: =A2 In cell B4 is the formula: =A2 In cell B5 is the forumla: =A3 In cell B6 is the formula: =A3 (we can see where this is heading..) The problem I have is that I want to use the fill handle to copy this series down a whole column, however Excel just won't see the pattern here! (or I'm missing something obvious) Help please!!!! :confused: -- jhct ------------------------------------------------------------------------ jhct's Pr...