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
1500 Views

Similar Articles

[PageSpeed] 7

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:

mirriring columns of data
Hi there, hopefully someone can led me a hand: Basically I have about 63K rows of data and 52 columns. What I need to to is flip the columns around so that the data in Col BC is in D Col D and vive versa. The header in the top row (row 5, starting in Col D) runs from 0-51, so it's basically a matter of sorting on this row (for less data, I'd transpose, sort and then transpose back- simple, but I don't have 63k columns!) If anyone can offer me a suggestion, I'd appreciate it (complete novice at VBA though, so bear with me). For reference I tried modifying the macro found he...

Column formatting help!
I need a way to wrap text from column A to B, then back to A on the next page. Can anyone help??? Again I need to wrap from column A to B on one page, then when it flows to the next page, it should go back to A then to B and so on.... Hi, I believe that you need 'Newspaper Columns' feature of MS Word. Bill Wrote: > I need a way to wrap text from column A to B, then back to A on the > next > page. Can anyone help??? Again I need to wrap from column A to B on > one > page, then when it flows to the next page, it should go back to A then > to B > and so on...

Formatting in columns
Hello. I have a report that lists name and phone extension across multiple columns in a report. Is it possible to insert dots between the name and the extension? They are two separate fields from a query. So far I have been unsuccessful. Many thanks for any help. Bird Byte wrote: >Hello. I have a report that lists name and phone extension across multiple >columns in a report. Is it possible to insert dots between the name and the >extension? They are two separate fields from a query. So far I have been >unsuccessful. > Not a simple problem. There are two...

Export records to excel columns
Hi folks, first post on here but been browsing for a while and found this is the most useful tool by a country mile for a novice like me to make an access database. Thanks for all the help. I'm currently making a system for work to handle all our service calls, stock control etc. I've ran into a problem that I just cannot find an answer to. I need to output an excel file where all records from a query are placed in separate columns, not rows. This is necessary because I then have to review the file, convert it to text and upload it via manufacturer's website. It can't be...

How to delete the column of impact and probability from Risks list
Hello, Is there a way to delete the default Probability and Impact columns under Risks in Project Web Access/SharePoint? Our client wants to use a 1-5 rating for each, so we've implemented drop-down menus in place of the default numeric fields. If we can get around having to create a new 'Risks' list, based on the default but without Probability and Impact, that would be great. Any help is much appreciated.... ...

Trouble adding non-adjacent cell columns to a print area
Hi, I can't seem to add non-adjacent columns to my print area in Excel 2000 and don't know what I'm doing wrong. I'm defining my area (in page break view) by 1st selecting the 1st 4 columns (which happen to be adjacent) and setting them as the print area. I then select each additional non-adjacent column, right-click a cell in it and select "Add to Print Area". According to the Help text, this is supposed to work. But all I ever get is the first 4 (adjacent) columns. The rest don't print or display in print preview. So what am I doing wrong? I've t...

Help in making a simle column/line chart in excel using vb6
Hi, I want to make a simple column or line chart in excel using vb6. I have created instance of excel and in the sheet1, I am able to populate some data. Now using that data, say the range is A2-B6, I want to use that range and make a chart. Pls help by giving me code, step by step. Thx Sandeep Sandeep - Step 1. In Excel, turn on the macro recorder while making the kind of chart you want. Step 2. Refine the recorded code. Here is some recorded code, which creates an embedded column chart, adds axis and chart titles, and does a little formatting: '' START RECORDED CODE Sub...

Convert Column Number to Column Letter
I have the last column of data. I want to use a range to say copy or define something. So how do you convert the number LastCol to a letter to display in a Range: LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column A web search for "convert column number to letter" turned up several hits, one of which was: http://www.mrexcel.com/forum/showthread.php?t=102298 look at the last 3 posts in that discussion. Two ways to get the job done. Personally, since you've gone the route you have at this point, I'd probably go with the solution in the last ...

Total this column
I have a column of numbers that look like this. 1805 (1591) 1351 (1078) 1318 (1117) 418 (394) 344 (306) 260 (247) 247 (226) 205 (142) 156 (139) 144 (109) 142 (117) 139 (122) I want to total the part NOT in the parentheses. How do I remove the part in parentheses OR total without removing. Any help here will be appreciated. Thanks in advance Dave One method. Insert a column right or left of this column if you want to retain the original format. Copy the column of data to the inserted column. Data>Text to Columns>Delimited>Next>Space>Next Select the column with the...

Line
I'm plotting monthly and ytd data as columns on the x axis. And for the y axis, I'm showing a percentage that relates to the monthly data, not the ytd data. I've formatted the 4 sets of columns to "0 overlap" and "100 gap width". So, for each set of columns (month and ytd), the month and ytd columns are touching. Currently, the data point for percentage line on the y axis sits in the middle of each set of columns. But since the percentage data point really only relates to the monthly data, I was hoping to shift the line so the data points are over th...

sorting linked columns?
I have columns of data that I would like to sort. The only problem is if I sort one column, the associated rows in the adjoining columns do not sort with it. Is there a way to link rows of data so that when a column is sorted the rows are sorted with the column? Thanks. Robert Hi Mark the whole range of your data before performing the sort choosing your column as the first sort column. that way, all the other columns adjust with the chosen column. -- Regards Roger Govier "Mr. Newt" <lektrikpuke@_yahoo.com> wrote in message news:ceidnQkVs_tROe7ZnZ2dnUVZ_uidnZ2...

How do I make column headers viewable as I scroll down the workshe
Without hiding rows, how do I make column headers viewable as I scroll down the worksheet? Select a row below the text you want to show, then use Window/Freeze Panes Bob Umlas Excel MVP FYI, I'll be leading a LIVE 1-hour FREE webinar on tips & tricks on January 27 from 4-5 PM est. It's done from your computer. To find out more & register, go to http://www.iil.com, click on the yellowish rectangle on the left "Try a free webinar", click the link for Microsoft Tips & Tricks. Maybe I'll "see" you there! "dogdigaly" <dogdigaly@discuss...

Suppress printing of hidden columns
I have a worksheet that is set up with page breaks between every column. Some columns are hidden, and I have a column that repeats on each page. I've selected all visible rows/columns in my print area, but when I preview/print, I am getting pages for each of the hidden columns that have just the repeated column heading but no data. Do you know of any way to suppress printout for hidden columns? Excel still respects those pagebreaks. So I'd either remove them or copy the visible cells/columns to a different worksheet and print from there. Gary Potvin wrote: > > I have ...

How to automatically fill a column?
How can I express this? Assume 2 columns, A and B. In A, a col of numbers: 1, 2, 3, etc. In B, now empty, I need to have a col of words that describe in words what the numbers stand for: apples (for No 1), oranges (for No2), lemons (for No 3), etc. I already have the col with the numbers, Column A. I need a way to tell Excel to fill in col B with the word "apple" wherever it sees a "1" in col A. And to fill in col B with the word "oranges" whereever it sees the number "2" in col A. etc, etc. Is this do-able? Best Regards, Arthur Think VLOOKUP wou...

Q: How to automatically adapt column width with XML format?
Hi. I'm developing an application that outputs its results to an XML file (with Excel XP's DTD). The trouble is the column width, which is set at default. Is there an XML tag which allow to adapt width automatically? I thought about a VBA macro, but I noticed that VBA code isn't stored in XML Excel's files. Thanks in advance, Francesco. ...

How do I put one heading over 3 narrow columns?
I am trying to make an excel sheet that has the year "2006" over 3 narrow columns that just have an "X" in or they are blank. I cannot figure out how to put the 2006 so that it shows especially when I print sheet. Select your 3 cells (say A1:C1) format|cells|Alignment tab|Horizontal dropdown|Center Across Selection And type your value in A1 (the leftmost cell) with nothing in the other two cells. Janice wrote: > > I am trying to make an excel sheet that has the year "2006" over 3 narrow > columns that just have an "X" in or they are bl...

how can I password hidden columns
I have a workbook which a colleague uses to enter data. The data is then manipulated on the same worksheet, but if these columns arehidden they can easily be unhid. Is there a way of password protecting these consecutive hidden columns, or an alternative please You can hide the columns, then protect the worksheet (tools|protection|protect sheet). But this protection is easily broken. If you have stuff you don't want others to see, don't put in an excel workbook that you share with others. David Allen wrote: > > I have a workbook which a colleague uses to enter data. The...

Total Change column in portfilio view
Hi there, for mutual funds, the TotalChange column in the portfolio view never seems to update for that day. This only happens for mutual funds, not stocks. The updated quotes for those funds download fine however and the +/- change is indidated fine. Any ideas? I've tried removing and re-adding the column and doing a file repair... This is in Money 2006. In microsoft.public.money, Eric wrote: >Hi there, for mutual funds, the TotalChange column in the portfolio view >never seems to update for that day. This only happens for mutual funds, not >stocks. The updated quot...

Chart Text columns
Is it possible to make a pie chart with text only in Excel columns. Example: column with different city names and then create a chart that compiles the percentage of cells with each of the different city names? Thanks in advance.... @ Hi you have to create a list of the frequency of each 'text' first on your own :-) You could use for example a pivot table to create such a list. See: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.tech...

Disabled "Print column or row titles on every page" on the sheet tab in page setup.
How do I enable the "Print column or row titles in every page button? In page setup it is disabled (grayed out.) I expect you came at it from Print Preview. Use File | Page Setup and open the Sheet tab; it will be available here. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Rich Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message news:PoYof.3239$vJ4.499@trnddc07... > How do I enable the "Print column or row titles in every page button? In > page setup it is disabled (grayed out.) > If you are tr...

Newbie question: how to convert one column to multiple columns?
Anyone, I have a spreadsheet of about 50 names/addresses in which all of the data is in a single column, i.e.: name1 address1 city1 state1 zip1 name2 address2 .... etc. I'd like to convert this spreadsheet so that the names are all in column A, the addresses in column B, etc. I'm certainly willing to select all five related cells for a given person (I would need to do that 50 times), but once those five cells are selected, is there a way that I can recast those five cells from being in the same column into multiple columns? Thanks so much! David You can easily do that with a fo...

Excel 2000... Missing row and column designators. #2
...

column widths
How is it that Microsoft made a product, CRM 1.2, that has columns where you can only adjust the width to be smaller? Moving the margin of a column in EITHER direction makes the column smaller. This is nonsense. The column widths in CRM don't work like Word or Excel or any other program I've ever used that has adjustable column widths. uhmmmm........what are you talking about? they work for me..... -Gary "Steve Talley" <stalley@clarityvisual.com> wrote in message news:e3f701c43c48$89214f70$a601280a@phx.gbl... > How is it that Microsoft made a product, CRM 1....

Networkdays and misleading results from blank column
Hello, I am working with two columns of dates and need to work out the number of working days between two dates, column A and column B with result in Column C. However in some instances column B is blank. Example: Column A = 14/01/2010 Column B = 23/01/2010 Result = 7 & thats ok. Problem is when column B is blank because the result would then come back as -28708. I am using the following formula: =NETWORKDAYS(A1,B1,Holidays). How could I add a condition to this formula that would translate 'if A1 and B1 are not blank then calculate number of days, if B1 is blank ...

Convert row to column?
How do I do the following: I have a an email group - long list. When expanded, it is --last name, first name (email); It looks like: Adams, Sam (adams @ sam.com); Baker, George (baker @ george.com); etc. I want to create a COLUMN in excel consisting of each name and email address (they don't need to be separated - just one name and address per cell going down the column in order). The unique separator would be the ; . How do I do this? I've tried importing in various ways but they always end up in one ROW which is not what I need. Thank you. Not clear where this long...