Auto fill a column

I would like to create a macro that fills a column for me from the first 
cell.  My dilemma is that the amount of rows of data are ever changing, and I 
would like the macro to fill until there is no more data.  Any help would be 
appreciated.
0
Utf
12/8/2009 3:46:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
666 Views

Similar Articles

[PageSpeed] 6

If I know my data, I can usually pick out a column that always has data in it if
that row is used.

If you can do the same, you could use code like:

Option Explicit
Sub testme()

    Dim wks As Worksheet
    Dim LastRow As Long
    
    Set wks = Worksheets("Sheet1")
    
    With wks
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("A1:A" & LastRow).Formula = "=c1&""- -""&d1"
    End With
End Sub

I filled A1:A(lastrow in B) with a formula that concatenated the value in column
C with "- -" and then the value in column D.

If this doesn't help, you may want to give more info--where the top cell is
located, which column can be used to determine the lastrow (if any) and how that
range should be filled.

dwake wrote:
> 
> I would like to create a macro that fills a column for me from the first
> cell.  My dilemma is that the amount of rows of data are ever changing, and I
> would like the macro to fill until there is no more data.  Any help would be
> appreciated.

-- 

Dave Peterson
0
Dave
12/8/2009 5:07:26 PM
Reply:

Similar Artilces:

Filling drop down box
hi, I have a drop down box in a cell. Based on a value of anohter cell I have to fill my drop down box. what i mean is, say if GreeNPackage is "No" then I want the drop down box to fill a range of values. But if the GreeNPackage is "Yes" then I want the drop down box to fill another set of values. how can i do this? plenty of thanks See reply in .Functions Biff >-----Original Message----- >hi, > >I have a drop down box in a cell. Based on a value of >anohter cell I have to fill my drop down box. what i mean >is, say if >GreeNPackage is ...

How to substitute for a non-existing column in a joined table
Hi, Is there a simpler way than a UNION to return a default value of a joined table for which a corresponding row does not exist? The following example (not a working one, of course) illustrates what I'm after. I'd like to return 'N/A' as c2name if there is no matching row in t2 SELECT t1.c1 (CASE t2.t1pk WHEN NULL THEN 'N/A' ELSE t2.name END) AS c2name, FROM t1 LEFT JOIN t2 ON t2.t1pk = t1.pk Thanks. On 2010-04-21 21:05, bob wrote: > Is there a simpler way than a UNION to return a default value of a joined > table for which a ...

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

FORMATING COLUMNS..... HELP
I need to format columns to allow only 7 characters and the rest of the data to go into column B -- JTEFUN "JTEFUN" <JTEFUN@discussions.microsoft.com> wrote in message news:6C5C7A99-E83F-430E-9576-6D1DB57B9311@microsoft.com... >I need to format columns to allow only 7 characters and the rest of the >data > to go into column B > -- > JTEFUN > If you mean that if a user types a lot of data into the one cell and that when they reach 7 characters the rest of the data is automatically inserted into the next cell, then I don't think you can do that....

Rows and Columns Settings Problem
How do you set rows and columns in a way that when you scrol down/column you can always see a certain row(s)/column(s) -- Message posted from http://www.ExcelForum.com Check out XL Help for "Freeze Panes" In article <JMorgan.1ad5vf@excelforum-nospam.com>, JMorgan <<JMorgan.1ad5vf@excelforum-nospam.com>> wrote: > How do you set rows and columns in a way that when you scroll > down/column you can always see a certain row(s)/column(s). Thank -- Message posted from http://www.ExcelForum.com ...

Question re:clustered column w/3D visual effect
I have Excel 2003 Why wont the 3D chart allow you to drag it more open? There is a large open area between the left side and the axis that looks jerky :) In a plain clustered column chart you can click inside to make the frame appear and drag it larger or smaller as you desire. In the 3D, clicking only allows you to change the angles of the 3D box. Am I missing something? thanks, Meenie This should be a hint to avoid the 3D effects. 3D charts are inflexible, but more important, the 3D effects mask the information in the chart. - Jon ------- Jon Peltier Peltier Technical Services, Inc....

BULK Conditional Formatting
I've read the posts on conditional formatting for cell colour based o another cell's value (eg. set the conditional formatting to "formul is" and then "=A1>0" and set the colour as red / blue / whatever...) however wondering if I can do this for an entire column withou individually changing the conditional formatting for each cell one b one (as there are over 400 rows). Basically I need a formula that reads the contents of column B for th particular row that is active. Can anyone help -- Rob Moyl ---------------------------------------------------------------...

Charting depending on criteria & data series name as a column val
I want to chart some prices as I want to take a look at price trends. My problems, and I can not figure out how to solve them, are: 1. Is it possible to dynamically change the chart depending on certain criteria (product family & selected customers) 2. As the number of customers is variable and they are in one of the columns. Is it possible to plot a series (customer name), depending on the name of a column? 3. I want to chart the data based on the date, but just include the dates available, to prevent periods of time showing no change (e.g. I don't want monthly ticks for the x a...

How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...

columns changed to numbers instead of letters?
I noticed my excel clumns have changed from letters to numbers and forumlas now look like =RC[-1]*R[-3]C[6] I was trying to make the R[-3]C[6] static (using the $) but it errors. Thanks Mike In Excel Options uncheck "R1C1 Reference Style" Gord Dibben MS Excel MVP On Thu, 28 Oct 2010 20:25:30 -0400, Mike <no_please@not.com> wrote: >I noticed my excel clumns have changed from letters to numbers and >forumlas now look like =RC[-1]*R[-3]C[6] >I was trying to make the R[-3]C[6] static (using the $) but it errors. >Thanks >Mike ...

How do I remove cross hatches in Sigma column
Addition of a column results in cross hatches. How do I get rid of the cross hatches and simply have the sum printed in the column? Widen the cell so the resulting value will fit. Excel does this so you don't accidentally SEE a number that's LESS than the actual number, which is what would occur if the column wasn't wide enough. ************ Anne Troy www.OfficeArticles.com "YHESSLER@EXAMPLE.COM" <YHESSLER@EXAMPLE.COM@discussions.microsoft.com> wrote in message news:08545D7F-E1B3-4879-A7DB-A2B723DDCD3F@microsoft.com... > Addition of a column results in cro...

Column names
Is there any code to convert column(number) to column(letters), and V.V. e.g. 27 for AA, AB for 28 Thanks =COLUMN(AA:AA) -- Regards, Peo Sjoblom "daniel chen" <danchen@worldnet.att.net> wrote in message news:eRRce.675942$w62.535613@bgtnsc05-news.ops.worldnet.att.net... > Is there any code to convert column(number) to column(letters), and V.V. > e.g. 27 for AA, AB for 28 > Thanks > Hi, Peo Great! Thanks. How about the other way? i.e. 27 for AA "Peo Sjoblom" <terre08@mvps.org> wrote in message news:%23MpiXScTFHA.2560@TK2MSFTNG...

comparing 2 similar columns on seperate work sheets in 1 workbook
How can I compare a column from worksheet 1 to a column in worksheet 2 of the same work book? BTW, the cell my have a number or text in it but not formulas "Dan" wrote: > How can I compare a column from worksheet 1 to a column in worksheet 2 of the > same work book? What are you looking for? If you want to find out if each cell in Column A of sheet1 appears in column A of sheet2, you could use a helper column of formulas: =isnumber(match(a1,sheet2!a:a,0)) and drag down And you could use the same kind of formula that will tell you if values in sheet2 appear in sheet1. ...

Restoring auto-archive properties
I have set up my archive folders to archive each sub-folder of my inbox separately. I have since found this too convoluted to search for items and i wish to restore the default settings so that, my inbox and its subfolders now appear as a subfolder of the Archive folders drop down. How can i do this without losing any of my previous data/e-mails? Simply change the settings and move over any item that you want to be somewhere else now. If this results in multiple empty pst-file, then you can disconnect those via; File-> Data File Management... -- Robert Sparnaaij [MV...

Add numbers accross columns after stripping away text
I have the following data in a spreadsheet: A1 B1 C1 D1 E1 F1 4.5f 6f 3.5f 3f 7.25f I need to be able to add the numbers together to give me 24.25, i.e. strip the fs away. The numbers will always be less than 10 and the there will only ever be .25 or.5 or .75 after the number (I don't know whether that is significant?). If anyone can show me how to do this I would be very grateful. I have been messing around with MID and FIND to no avail and then started thinking that SUMPRODUCT might have to get involved but it all got a bit m...

pivot table, How to add 1 column
I just need to add one column only but pivot table create another one? I thinkc because I have a column with 2 parameters. I can turn on / off with the field drop down menu How can I get around with this problem? Thanks Daniel ...

Cross referencing named row with named column
I'm trying to find a way to cross reference a column named June with a row named Sales but cant figure out what the syntax is. I thought it used to be =June|Sales but it doesnt seem to work. Hi try =INDEX(A1:H20,MATCH("June",A1:H1,0),MATCH("Sales",A1:A20,0)) -- Regards Frank Kabel Frankfurt, Germany "davide212" <davide212@discussions.microsoft.com> schrieb im Newsbeitrag news:917620B9-9182-45C8-9D0F-56B0878CF617@microsoft.com... > I'm trying to find a way to cross reference a column named June with a row > named Sales but cant figure out ...

Form Opening with Blank Column(s)
I have created a couple applications that are working well on our remote access server, Access 2003. However, when I have people put the application on there standard desktop on the network, one or more columns appear blank when the form is opened in Access 2003. Any ideas why the data retrieval or function would change between our server addition of Access vs client workstations? -- Message posted via http://www.accessmonster.com I have one user in my organization that is experiencing this problem also. The problem started the week of the 25th of February. The user could not give me a ...

How to get task manager CPU and default memory column values
I need to return CPU and memory columns for each process in server from web service like task manager shows by default. I tried code below but s.TotalProcessorTime throws Win32exception with stack below. How to get CPU and real memory size from server using web service ? Andrus. static void Processes(StringBuilder sb) { List<Process> processes = new List<Process>(); foreach (Process process in Process.GetProcesses()) processes.Add(process); processes.Sort((a, b) => { ...

Sum alternate columns over a large (>100) range
I need to sum alternate columns over a very large number of columns. Is there an easy way to do this? Sum(A1+C1+E1+G1 .... ) seems to bomb out after about 32 entries and I need more than this. Any Help, Thanks, Try instead, something like: =3DSUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=3D1),A1:Z1) or for all 256 cols A to IV (i.e. the entire row: A1:IV1) =3DSUMPRODUCT(--(MOD(COLUMN(1:1),2)=3D1),1:1) -- Rgds Max xl 97 --- GMT+8, 1=B0 22' N 103=B0 45' E xdemechanik <at>yahoo<dot>com ---- Kanga 85 wrote: > I need to sum alternate columns over a very large number of columns...

fill cbo from input into txtbox
Hello. This is going to be a peculiar issue but the db was done long ago by someone else on a dark and drury night and this is a request from the users. I have a form with a txtbox that has zip codes entered into it. On the same form there is a cbo that has a query for a rowsource. This rowsource only has 7 items to it that provides a regional code to the field based on the zipcode entered. There is a table that has the established zipcodes and corresponding regional code. There can be instances when the user enters a zipcode and there is no corresponding zip code. There are also...

Replace data in a column
How can I replace data in a column that is between 1 and 10? This way it will leave any other text there. I think the whole column is formatted as TEXT so I may be running into a problem there. Will I still be able to create some sort of formula If data >1 or <10 then replace with " " -- Thanks One way... Select that column apply data|filter|autofilter use the arrow dropdown and choose custom Greater (or greater than or equal to) 1 AND less than (or less than or equal to) 10 Now select those visible cells type your new entry hit ctrl-enter instead of just enter to fill...

contribution percentage column in pivot table
I have a pivot table I've created in Excel from 120,000 lines in M Access. It's basically across time at the top level, with months from Jan t Dec, starting: January Category A Category B Person A 10 5 Person B 7 12 I need to get a running percentage contribution column for each MONT from each category, ie a column next to Category A showing 67% (bein 10/15) and again next to Category B for 33%; without it being the tota of that entire row. Help!! -- kpritchet ---------------...

How do you remove auto indentation in text on PowerPoint?
I'm entering text on a 'Content' slide and the second line is automatically indented. I need this to line up on the leftside with the first line of text. The alignment is set for the left. Thanks. If you cannot see the ruler View > Ruler Then with the text selected move the bottom grey pointer to the left. Holding down CTRL may help. John "SherriG" <SherriG@discussions.microsoft.com> wrote in message news:E641735D-48C0-41BA-8DDE-783DAA26F4C1@microsoft.com... > I'm entering text on a 'Content' slide and the second line is ...

How to total a column of cells without using Autosum
Help me remember please. I used to be able to quickly total a rance of cells by simply highlighting them. The total would then appear in the fx box. I can't seem to remember how do do this. I'm using Excell in Office 2003. Thanks Hi, Select a cell directly under or to the side of the range and click the auto-sum button (sigma) button -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Goldar" wrote: > Help me remember please. I us...