How do you add a row to an Excel Range? 1

[BNPMike]

On Sheet N I have a table/list/range with a varying number of rows. How do I add/append a new row?

Obviously I could type it in - I'd look down the page and see where the first empty line is. However how do I do this programmatically?. I can find out where the last row is but that's a big step from getting Excel to insert new data at that point. The new data might come from a user typing data into another sheet or from a web query on another sheet.


Or do you normally use a database call to store varying 'tables'?

 

[N1GHTEYES]

At the risk of being a bit obvious, have you tried recording a macro while doing it manually?

Tony

 

[SkipVought]

hi,

VBA (macro) questions are best addressed in forum707.

You can use the End Method to find the end of any range with contiguous data...

dim lRow as long
'assuming that your table starts in A1 on Sheet1...
With Sheets("Sheet1")
   lRow = .Range(.Cells(1,1), .Cells(1,1).End(xlDown))
End With

lRow is the LAST row containing data in column A, assuming that your table has no empty rows and nothing BELOW the table.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BNPMike]

OK - Sorry. I meant in Excel rather than VBA, so can I do it in Excel or do you have to resort to a database or VBA?

I can only think you decide on a fixed range and then get every row to refer to the input range with some sort of IF based on whether the row number is one greater than the 'bottom' of the filled rows. However the dependancies/order of execution is not obvious.

Is there a standard way of adding/updating/deleting 'tables'?

 

[SkipVought]

Is there a standard way of adding/updating/deleting 'tables'?

Don't have my laptop so I can't say exatly, but there is a built-in feature that allows ADD, CHANGE, DELETE, FIND in a table for maintenance of your table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

faq68-1331

Gavin

 

[BNPMike]

Gavin

You seem to be making a table variable so that once a new record is appended, you can access the new row without knowing its location.

I don't see how what you've got is about actually entering that new row.

What's unusual about this question is normally if you do the same thing in Excel it always has the same outcome. However in this case I may add "Smith" and "20" to Sheet1!A1 and B1 and the data is mirrored in Sheet4!C200 and D200 but when I enter Smith and 20 again in A1 and B1, they appear in C201 and D201.

All the elements in Sheet4!C refer to A1 and B1 - that's where their data will come from, but only when it's "their turn". Just off hand I can't see how you are going to stop every row thinking it's its turn. If you said something like "I equal A1 if I am currently the row after the last filled row", then as soon as I take A1, the next row mightthink it's its turn depending on the order of execution. If Excel evaluated Sheet4!C bottom upwards, you'd be alright. However if it went top downwards then the entry would cascade all the way down.

 

[SkipVought]

I never had a row, think any thing about a turn?????

Exactly what do you mean?

How does sheet4 reference sheet1?

Sample data might be helpful.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BNPMike]

How does sheet4 reference sheet1?"

That's probably the question I'm asking you.

Just to make it clear. I type in "Smith" "20" on sheet 1 and "Smith" "20" appears in the first empty row below the existing data on sheet 4.

This is such a basic behaviour of a programming system, there must be a natural way of doing it.

 

[combo]

Excel is not a database. Of course one can arrange data in tables, but one needs additional tools to manage data (VBA, ADO, MSQuery).

The simplest one can do is to use built-in data form. Using one line VBA code it is possible to display the form for data in hidden sheet. As a result one can store data in hidden sheet, have a form for finding and editing data (incl. formulas, that are extended automatically and locked for edition), a visible sheet for record visualisation and maybe another sheet for pivot table summaries.




combo

 

[SkipVought]

A reference implies a relationship, like a formula or a linked table.

You can easily accomplish what you stated, using a PivotTable on sheet4, referencing the data on sheet1. You enter data on sheet1, go to sheet4, refresh the PivotTable and, VOLA, y'all, its there!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BNPMike]

Skip

The relationship is as I said before. For any cell on Sheet 4 (the database) it equals the input cell on Sheet 1 if and only if this cell is the first empty row. Otherwise it equals what it is already (which could be an existing value or empty if it is beyond the first empty row).

First problem is I can't see how to say D201 = IF(<lastrow+1>,Sheet1,D201) because Excel sees that as a circular reference.

Perhaps some background would help.

I am trying to use Excel as a web programming language. So we have a Department Biscuit App. People come in in the morning and log on, and enter name and number of biscuits they want today. The Web server picks up "Smith" an "20" (he's got a meeting) and slots into the first sheet of a workbook. The Excel book enters this data into its database on Sheet 4. At 10;00 the administrator runs another spreadsheet that refers to the database, adds up the total number of biscuits requested etc.

Excel is used as a database all the time everywhere, but where ever I've seen it, it rests on a user opening the sheet, looking where the last row is and entering data manually afterwards. That doesn't work in a web context. Excel must work as a program not as a user interface. The problem is of course Excel can never tell one cell to load another. It always says the target cell must derive its value, not be given it. Thus for a database, every cell has somehow to know whether it is supposed to be loading itself or whether another cell is actually the one that should receive the data entered.

I can see it's a problem but I would have thought something would have been added to solve it by now.

 

[SkipVought]

You cannot do this with formula references. As previously stated, you must use VBA.

as posted previously, to find the last row of data on a sheet in a column...

dim lRow as long
'assuming that your table starts in A1 on Sheet1...
With Sheets("Sheet1")
   lRow = .Range(.Cells(1,1), .Cells(1,1).End(xlDown))
End With

Further exploration of this issue, would be best addressed at forum707, as previously stated.

I'm sorry if you feel that Excel SHOULD be able to do something different. If you need a tool that cuts a mortise and tenon, but you only have one that cross cuts, then you need to invest in THAT other tool.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BNPMike]

Thanks Skip. I guess you are confirming I need to make database calls for database activities (although I'm going to persevere with MS Query - I'll get it to work one day).