Prefill column

Below is a sample of the data I download to a spreadsheet.  I need to write a 
macro to fill in the empty cells in column A with the data from the previous 
cell in the column with data.  I know I didn't explain that well but in the 
example below, I need TF48 to fill in Col A for rows 3,4 & 5.  Then T16A 
would fill in row 7 and T505 in row 9 and 10.  I hope I've explained this 
sufficiently.  Thanks in advance for your help.

1     A             B             C             D
2   TF48    $100.00     ABC       20091116
3              $  50.00     DTR       20091116
4              $  25.00     PLO       20091116
5              $  75.00     FRD       20091116
6   T16A   $100.35     UYE        20091116
7              $124.55     PLE        20091116
8   T505    $109.00     REW      20091115
9              $876.90     JLO        20091113
10            $234.90     REW       20091116
0
Utf
11/16/2009 3:05:01 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
1361 Views

Similar Articles

[PageSpeed] 43

This seems to work for me

Sub tryme()
 mylast = Cells(Cells.Rows.Count, "B").End(xlUp).Row
 mytext = Cells(1, "A")
 For j = 2 To mylast
   If Cells(j, "A") = "" Then
     Cells(j, "A") = mytext
   Else
     mytext = Cells(j, "A")
   End If
 Next j
End Sub

Save file before trying it - just incase!
best wishes
-- 
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"SITCFanTN" <SITCFanTN@discussions.microsoft.com> wrote in message 
news:795FECD4-A140-4C50-B6D6-76181DED74E4@microsoft.com...
> Below is a sample of the data I download to a spreadsheet.  I need to 
> write a
> macro to fill in the empty cells in column A with the data from the 
> previous
> cell in the column with data.  I know I didn't explain that well but in 
> the
> example below, I need TF48 to fill in Col A for rows 3,4 & 5.  Then T16A
> would fill in row 7 and T505 in row 9 and 10.  I hope I've explained this
> sufficiently.  Thanks in advance for your help.
>
> 1     A             B             C             D
> 2   TF48    $100.00     ABC       20091116
> 3              $  50.00     DTR       20091116
> 4              $  25.00     PLO       20091116
> 5              $  75.00     FRD       20091116
> 6   T16A   $100.35     UYE        20091116
> 7              $124.55     PLE        20091116
> 8   T505    $109.00     REW      20091115
> 9              $876.90     JLO        20091113
> 10            $234.90     REW       20091116 

0
Bernard
11/16/2009 3:29:07 PM
This macro should be pretty speedy...

Sub FillInTheBlanks()
  Dim Area As Range, LastRow As Long
  Const ColLetter As String = "A"
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  For Each Area In Columns(ColLetter)(1).Resize(LastRow). _
                   SpecialCells(xlCellTypeBlanks).Areas
    Area.Value = Area(1).Offset(-1).Value
  Next
End Sub

-- 
Rick (MVP - Excel)


"SITCFanTN" <SITCFanTN@discussions.microsoft.com> wrote in message 
news:795FECD4-A140-4C50-B6D6-76181DED74E4@microsoft.com...
> Below is a sample of the data I download to a spreadsheet.  I need to 
> write a
> macro to fill in the empty cells in column A with the data from the 
> previous
> cell in the column with data.  I know I didn't explain that well but in 
> the
> example below, I need TF48 to fill in Col A for rows 3,4 & 5.  Then T16A
> would fill in row 7 and T505 in row 9 and 10.  I hope I've explained this
> sufficiently.  Thanks in advance for your help.
>
> 1     A             B             C             D
> 2   TF48    $100.00     ABC       20091116
> 3              $  50.00     DTR       20091116
> 4              $  25.00     PLO       20091116
> 5              $  75.00     FRD       20091116
> 6   T16A   $100.35     UYE        20091116
> 7              $124.55     PLE        20091116
> 8   T505    $109.00     REW      20091115
> 9              $876.90     JLO        20091113
> 10            $234.90     REW       20091116 

0
Rick
11/16/2009 3:34:18 PM
What about that?

Sub FillBlanksColA()

Dim rng As Range
Dim rCell As Range
Set rng = Range(Cells(1, 1), Cells(1, 2).End(xlDown))

For Each rCell In rng
    If rCell.Value = "" Then
      rCell.Value = rCell.Offset(-1, 0).Value
    End If
Next

End Sub
0
chg
11/16/2009 3:44:58 PM
try this
Option Explicit
Sub test()
    Const WORKSHEET_NAME As String = "Sheet1"
    Const WHATCOLUMN As String = "B"
    Dim i As Long
    Dim sString As String
    i = 1
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(WORKSHEET_NAME)
    
    Dim rng As Range
    Set rng = ws.Range(ws.Cells(2, WHATCOLUMN), ws.Cells(Rows.Count, 
WHATCOLUMN).End(xlUp))
    
    Dim Cell As Range
    For Each Cell In rng
        If Cell.Offset(0, -1).Value <> "" Then
        sString = Cell.Offset(0, -1).Value
        Else
        Cell.Offset(0, -1).Value = sString
        End If
    Next
    
End Sub

"SITCFanTN" wrote:

> Below is a sample of the data I download to a spreadsheet.  I need to write a 
> macro to fill in the empty cells in column A with the data from the previous 
> cell in the column with data.  I know I didn't explain that well but in the 
> example below, I need TF48 to fill in Col A for rows 3,4 & 5.  Then T16A 
> would fill in row 7 and T505 in row 9 and 10.  I hope I've explained this 
> sufficiently.  Thanks in advance for your help.
> 
> 1     A             B             C             D
> 2   TF48    $100.00     ABC       20091116
> 3              $  50.00     DTR       20091116
> 4              $  25.00     PLO       20091116
> 5              $  75.00     FRD       20091116
> 6   T16A   $100.35     UYE        20091116
> 7              $124.55     PLE        20091116
> 8   T505    $109.00     REW      20091115
> 9              $876.90     JLO        20091113
> 10            $234.90     REW       20091116
0
Utf
11/16/2009 4:26:01 PM
I guess I should point out why I said my solution "should be pretty 
speedy"... the difference between my loop and the others that have been 
posted so far is the number of iterations that will be performed. For your 
posted example data, the macro I posted will iterate 3 times (once for each 
blank area) whereas the other solution's loops will iterate 9 times (once 
per cell). I would also note that my calculation for the LastRow does not 
require you to know in advance which column will contain the row with the 
last piece of data in it... my code finds the last filled row of data no 
matter what column it is in.

-- 
Rick (MVP - Excel)


"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:uyf63ItZKHA.5276@TK2MSFTNGP05.phx.gbl...
> This macro should be pretty speedy...
>
> Sub FillInTheBlanks()
>  Dim Area As Range, LastRow As Long
>  Const ColLetter As String = "A"
>  On Error Resume Next
>  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
>            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
>  For Each Area In Columns(ColLetter)(1).Resize(LastRow). _
>                   SpecialCells(xlCellTypeBlanks).Areas
>    Area.Value = Area(1).Offset(-1).Value
>  Next
> End Sub
>
> -- 
> Rick (MVP - Excel)
>
>
> "SITCFanTN" <SITCFanTN@discussions.microsoft.com> wrote in message 
> news:795FECD4-A140-4C50-B6D6-76181DED74E4@microsoft.com...
>> Below is a sample of the data I download to a spreadsheet.  I need to 
>> write a
>> macro to fill in the empty cells in column A with the data from the 
>> previous
>> cell in the column with data.  I know I didn't explain that well but in 
>> the
>> example below, I need TF48 to fill in Col A for rows 3,4 & 5.  Then T16A
>> would fill in row 7 and T505 in row 9 and 10.  I hope I've explained this
>> sufficiently.  Thanks in advance for your help.
>>
>> 1     A             B             C             D
>> 2   TF48    $100.00     ABC       20091116
>> 3              $  50.00     DTR       20091116
>> 4              $  25.00     PLO       20091116
>> 5              $  75.00     FRD       20091116
>> 6   T16A   $100.35     UYE        20091116
>> 7              $124.55     PLE        20091116
>> 8   T505    $109.00     REW      20091115
>> 9              $876.90     JLO        20091113
>> 10            $234.90     REW       20091116
> 

0
Rick
11/16/2009 4:37:48 PM
Reply:

Similar Artilces:

Sort multiple columns into one column
Once again I am here with yet another problem... I have several columns of serial numbers that I need to sort into one column. Is there anyway to just select all of the serial and sort them automatically? Here is an example of what the spreadsheet looks like: COLUMN 1 COLUMN 2 COLUMN 3 WBLM015780 WBLM006249 WBLM006249 WBMM025115 WFMM041685 WBLM006249 WDMM020958 WFMM074766 WBLM006249 WDMM021095 WFMM074766 WBLM006249 And I need to put them all into one column. I have hundred of serial numbers in each column and it would be a pain just to cut and paste. Thanks in advance! --- M...

average multiple columns but skip a few columns
How can I average muliple columns that are not continuous? For example, I'd like to average A2 to Z2 but ignore the numbers in C2, F2 to H2? Thanks for the help =AVERAGE(A2:B2,D2:E2,G2,I2:Z2) HTH, Bernie MS Excel MVP "cpliu" <spamfreeliu@yahoo.com> wrote in message news:327da3d2-61f7-4e64-af17-9e49f85fa419@k19g2000yqg.googlegroups.com... > How can I average muliple columns that are not continuous? For > example, I'd like to average A2 to Z2 but ignore the numbers in C2, F2 > to H2? > > Thanks for the help On Dec 5, 7:04=A0pm, "Bernie Deitri...

Averaging columns #2
I tried Average, but it won't pass over the broken references either. Since I have these columns present with no data, I have a divide b zero error...I need the average to just ignore those columns. I am a n00b when it comes to Excel, so forgive me if I am overlookin something simple... Thanks, Garret -- GarrettSoclin ----------------------------------------------------------------------- GarrettSocling's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1622 View this thread: http://www.excelforum.com/showthread.php?threadid=27639 Hi you may change your f...

column A matches f places the value from g in column b what funct
I am trying to have excel enter information in cell b by finding a number in a, matching it to a cell in f and returning information from g that that coresponds with the number in a, example if column A has numbers 1-10, coulmn F has numbers 1-10, column G has information related to each number so if I place 2 in A, it searches column F for 2, places information from column G that matches in column B In B1 enter this formula. =VLOOKUP(A1,$F$1:$G$10,2,FALSE) Drag/copy down to B10 Gord Dibben MS Excel MVP On Sun, 15 Nov 2009 11:28:01 -0800, Daryl <Daryl@discussion...

Adding column data based on match in 2nd column
Hi, Simple problem, but got me stumped. The setup: I have 2 columns. The first (column A starting in row 10) has members' names. The second (column B starting in row 10) has the additions made by the members over time. Column B has a 'header' cell that adds up the values entered by a particular member. The header cell for column B is B9. Cell A1 contains the member's name that cell B9 has to add the values for. I need a formula for Cell B9. For example: [Cell A1] Member A [Row 9] Formula needed for cell B9 COLUMN A_______|| COLUMN B____ [Row 10] Member...

Column Width
I am using Excel 2000. When I drag a field name from the Pivot Table toolbar to the "row" area of the Pivot Table, the resulting column width is often inadequate to display the contents. Of course, I am not prevented from widening the column after the fact; however, it should ideally provide a "best fit" width automatically. Is there something I am missing here? You can turn on the AutoFormat option, and the columns widths should adjust to fit: Select a cell in the PivotTable From the PivotTable toolbar, choose PivotTable>Table Options Add a check mark to 'Auto...

How to add one column to another column?
I have a worksheet with product ID and Product Color Code and I like to combine the two columns into one. For example Column A Column B 123.445 AAA 124.892 ABC Combine together like this 123.445.AAA 124.892.ABC Thanks Rocky Rocky In C1 type =A1&"."&B1 and copy down -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Rocky Lam" <RockylamATyahooDOTcom> wrote in message news:OaBDHJt%23EHA.2584@TK2MSFTNGP09.phx.gbl... >I have a worksheet with product ID...

Row and column headings
I can get row and column headings to print on the first page of my Excel worksheet, but I can only get column headings on page 2 and row headings on page 3. Hi not really sure what your issue is :-) Could you re-state your problem -- Regards Frank Kabel Frankfurt, Germany Spenikba wrote: > I can get row and column headings to print on the first page of my > Excel worksheet, but I can only get column headings on page 2 and row > headings on page 3. ...

Formatting Columns in Excel
Howdy all, I have a problem with my spreadsheet that I'm hoping someone can help with. I have created a new spreadsheet and formatted the columns to suit the data that I am entering. I have other data emailed to me from another co-worker and whenever I cut and paste the data on to my master spreadsheet I have to re-format the columns that have been just added. My question is, is there a way to format the columns so that whenever date is cut and pasted to my document it will automatically change the formatting to default to the same as my master document? By the way, I created a duplica...

Text to Columns
Hello excel 2000 / 97 I quite often use Text To Columns to convert text to numbers so that for instance the same number doesnt show up twice in pivots just because one instance is formatted as text and another as numeric. Its usually very useful but I notice that sometimes it unexpectedly moves data upwards in a column. This particulary seems to affect new sheets: eg open new sheet Enter following values a2 5 a3 '5 a4 5 Select column A and do Data>Text to columns. The data seems to be pasted up by one row so I now have 4 rows of data. If you repeat these steps on the sam...

Excel
I'm new to Excel and would like to change all negative numbers appearin in a non calculated column to positive numbers. The problem result from a bug exporting Quickbooks Pro invoices to Excel, in th "quantities" column, and would like a quick macro to fix, versu changing each column entry to a positive number by hand. Thanks, Joh -- Message posted from http://www.ExcelForum.com try sub makeabs() For Each c In Selection c.Value = Abs(c.Value) Next end sub -- Don Guillett SalesAid Software donaldb@281.com "John E. >" <<John.E..15h9lk@excelforum-nospam.com&g...

Pivot: totals in the next column
Hi all, i need your help for the below table: ITEMS YEAR YRLY_TOT GRAND_TOT 2008 $10 1. ITEM1 2009 $20 $60 2010 $30 2008 $10 2. ITEM2 2009 $30 $80 2010 $40 2008 $20 3. ITEM3 2009 $30 $90 2010 $40 Each item has yearly totals (column YRLY_TOT). I want column GRAND_TOT to show total of all three years for each item ( i guess the three cells shou...

Create Pivot Table Data with Column "Sum" rather than "count" defa
I have a large set of data (20,000 rows and about 100 coulmns). I wish to select about 5 columns for a pivot "rows" and about 60 columns to place in pivot "data". Unfortunately, when I drag these columns into "data", all of them default to "count" of column. I then have to manually change each one to "Sum". Is there a simple way to change this default to "sum" without a 2nd step for each field added? If you have any non-numeric data in a field (empty cells are non-numeric!), then excel will use Count. But you could tak...

Create a new column using information from separate columns
Hello! Another question that I posted under Access, I hope this is the right place to ask this. I have a little bit of a problem, and I wasn't able to find the solution anywhere. I have column A "Title" that is linked with column B "Day of the week". Each title can have 1, 2 or 3 days of the week (which will then be column C, D, etc). It looks like this: TITLE DAY1 DAY2 DAY3 Math Mon Tue History Tue Fri Mon English Wed Mon How can I create a new column that lists all the "titles" that appear on Monday and a separate column fo...

Repeat values in column A a certain number of times depending on the value in column B
Imagine a set of data as set out below: Column A Column B Apples 24 Pears 36 Oranges 8 I want to poplulate a column (for exampel column A on a new sheet), where the values in column A will be repeated as many times as the value in column B Thus the first 24 rows will say Apples, the next 36 rows will Pears, Oranges. I need a formula that recogonizes that when it gets to row 25 that it should no longer need to copy Apples, but then switch to pears. This might sound like an unusual request but if I can grasp a way to do this I can create the table I need and populate the rest...

prefill
I enter ID and data on form 1. It gets stored in table A. I input same ID on form 2, for which I want a select number of fields (populated) from tableA to be displayed, then enter data into new fields on form 2 for which they will be stored in table B. I want the ID for both tables to be the same, so as the relationship is 1to1. (The PK are the same for each table).There is no changing what is on each form, and no making them subforms as this example is just a super watered down verison of what I have. I need help!!! (please no code use). How do I do this? - Noe -- Message posted via Access...

Sort whith 3 row column heading
I've searched the forum on sorting and can't find this problem. My shee in Excel 2002 has three rows of data that serve as column headings. need for others to be able to easily sort using the toolbar sort keys. When I do this, it doesn't recognize the column headings and sorts the too. If I choose the data range myself, it works - but the users nee a one touch simple method. Any ideas -- Message posted from http://www.ExcelForum.com Kathy, You need your column headings in one row. You can use Alt-Enter as you enter them to break into separate lines within the cell, or set ...

Prefill column
Below is a sample of the data I download to a spreadsheet. I need to write a macro to fill in the empty cells in column A with the data from the previous cell in the column with data. I know I didn't explain that well but in the example below, I need TF48 to fill in Col A for rows 3,4 & 5. Then T16A would fill in row 7 and T505 in row 9 and 10. I hope I've explained this sufficiently. Thanks in advance for your help. 1 A B C D 2 TF48 $100.00 ABC 20091116 3 $ 50.00 DTR 20091116 4 ...

After date entered, prefill another date field
Hello, I was wondering if anyone knows how to get one field to populate the same datainto another field? In other words: I have a date field "Funding Date" and when the user enters the funding date, I want the txtDateFrom1 field to automatically populate with the same date so the user doesn't have to enter it twice. If possible, please let me know and also, please simplify answer. Thanks!!! Use the After Update event of the Funding Date control: Me.txtDateFrom1 = Me.FundingDate -- Dave Hargis, Microsoft Access MVP "Stockwell43" wrote: > Hello, > ...

Sorting for column based on another column
I want to sort say column a1 to a30 which has names in it based on column b1 to b30 which has say widget sales to put column a1 to a30 in numeric order from high to low So that say: Mo 10 Larry 20 Curly 5 Billy 12 Bobbi 8 Would end up as Larry 20 Billy 12 Mo 10 Bobi 8 Curly 5 Thanks in advance. Hi click in any cell between a1 and b30 and choose Data / Sort from the menu choose whether or not you have a header row then in the first drop down box, choose column B (or its header) choose descending click OK Cheers JulieD <xxnonexnonexx@tampascanner.info> wrote in message news:...

Pivot table
Is there a way in pivot tables after selecting hide detail for a column that new values are also hidden? ...

Table Columns/Expanding Cell Width
I was creating my first table in Word 2007. I used Insert->Tables- >Tables. When I typed in text, the column kept expanding and the text did not wrap until it hit the end of the line and the adjacent cells were compacted to their margins. Then I did Table Tools->Auto Fit->Fixed Column Width and the column width stayed the same and wrapped. So now I create a new file and the do a table. Now the column width stayed the same. Not that I would want the initial behavior of columns expanding---why does it no longer appear? ...

divide column(x) by column(y) to give column(x/y) in excel?
I'm trying to divide one column by another column to give me another column of awnsers as in B1/C1=D1, B2/C2=D2 etc... Is this possible? Thanks In cell D1 enter the following formula: =B1/C1 Copy this formula down column D as far as you need. The cell references will increment automatically. HTH, Elkar "James" wrote: > I'm trying to divide one column by another column to give me another column > of awnsers as in B1/C1=D1, B2/C2=D2 etc... Is this possible? > Thanks ...

hiding rows / columns when printing
is it possible to hide items when printing? Hidden rows/columns will not print. You may do it manually or with a macro that hides/prints/unhides. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "sam" <sam@msforums.com> wrote in message news:upeKNWirJHA.5912@TK2MSFTNGP02.phx.gbl... > is it possible to hide items when printing? > ...

Print 1 Column in multiple Columns
I have a list of 400 names in Column A. I don't want to waste 8 pages printing out the data. Is there a way to have the data from that column print out in multiple columns like I would if the list was in Word? I copied it to Word and selected 3 columns and would only use 3 pages. I would keep it this way, but I am going to add and delete names from the list so it is something I want to keep in place. Thanks As Always, RIP Hi Have a look here: http://www.mvps.org/dmcritchie/excel/snakecol.htm#snakecols -- Andy. "Ripper" <anonymous@discussions.microsoft.com> wrote ...