Tracker
HOME    TRACKER    Excel

# Adding A Variable Rows Worksheet

## Due DateInvDocument numberDebitCreditBalance 17/09/08L01607610714211201825.44 13/10/08L01637010284345841020 13/10/08L01637131713874501020 13/10/08L016373852202473461020 13/10/08L0163748.5222E+111020 15/10/08L01641811103746832316 The above is a part of a w/sheet. Rather than determining the last row in the debit column and credit column (to know which is greater) and use a loop to put a formula in each of the cells in the balance column (adding the debit and credit), I know I can use the with ... statement to input the formula at a go , but cannot figure it out. Sub sumtotal() With Sheets(1) With .Range("G14", Range("G" & Rows.Count).End(xlUp)) .Clear .Formula = "=sum(E14+F14)" On Error Resume Next End With End With End Sub

Related Forum Messages:
Adding A Constant To Sets Of Rows Of Variable Size
I have data from 100 trials. Each trial has a variable number of data points (rows), but each row is labeled in the first column with the trial number. I would like a macro to identify the first row of each trial, calculate the difference of the value of the cell in column G of that row from a constant (718), and then add that difference to that cell and all other cells in that column of that trial.

So based on the attached sample, I would like all values in column G for trial 2 to be +1, for trial 3 to be -5, and for trial 4 to be +2.

In the file, I've cleared all the data from the impertinent columns. Eventually, I will also want to perform a similar process on column H but with a different constant.

Retrieving Variable Rows From Another Worksheet: Reducing "Calculation Time"
I'm using this formula to pick all rows from a huge range of cells "DATA" in another sheet, by matching the first column value (SAPDATA is the range that contains all the first column cells of DATA).

{=IF(ROW(\$B49)-ROW(\$B\$48)>COUNTIF(SAPDATA,\$D\$4),"",INDEX(DATA,SMALL(IF(SAPDATA=\$D\$4,ROW(SAPDATA)),ROW(\$B49)-ROW(\$B\$48)),2))}

All possible first column values have been stored as a dropdown in cell D4, and whenever I change the choice in the dropdown cell, the retrieval of those hundreds of rows takes ages in excel.

Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.

Per attached I have a range of columns (variable) which I need to sum, the number of rows are also variable. I have defined the variables:

firstrow
lastrow
firstcol
lastcol

I have tried the following code which isnt quite working.

I have few range of data that may range from 0 to 24 and the return to "0 " after 24 and the 2nd set of value keep repeating 0 to 24, but I would like to keep adding 24 when the value return to 0 and if the number less than 5 will be adding 24 and the number from 6 to 8 will be equal to 11 and also the number from 9 to 23 will not change. I have attached an example that Colum A needed to be converted to column B (which I done it manually).

Adding Variable To Specific Sheetname In A Sumif Function
Hi, looking for help desperately in fine tuning a formula. I have a formula at the moment (which works) for searching through a list on a separate file and totalling up all values which relate to it, see below:

=sumif([filename.xls]1’!\$B:\$B,D10,’[filename.xls]1’!\$H:\$H)

The tab ‘1’ in the formula relates to the first of the month so this month there are 28 different tabs with similar information.

With C10 containing the date in this instance, does anybody know a way of making ‘1’ a variable so that entering ‘04/02/10’ would change it automatically into a 4? (Unfortunately for me changing the 1 to =c10 didn’t work).

Adding A Form To A Worksheet
I'm adding a form to a worksheet for the first. I tried to follow an example from the internet and then adapt for own form but have got lost and don't know where.

Attached is the excel file i am working on. On the summary worksheet i have added a button which works fine and opens up the form i have made.

My problem is it doesn't enter the data into the relevant cells on the relevant worksheets. I think the form should be quite self explanatory.

Adding 2 Rows After Every One Row
I need some help fomulating a VSB code which would add two rows after every one row.

We have workook with several worksheets (tabs). The main page has several headings (category), including "Doors and Ceilings", "Walls" etc. There are currently three rows under each heading. When I go to a category (tab) there is a list of issues in column b. I can go to column A of each issue and if I place an "a" that issue will be copied to the main page under that category. The problem is that i only have 3 rows and I need a row added each time an issue is transferred to the cell before it.

Is there some VBA I can use to detect the precence of data anbd add a row?

I have data in several (50+) columns. I want to be able to sort all my data by the column I select with one click. So, I basically want a button over each column that sorts the data by that column. It would be ideal to have the same "Sort" macro that is attached to all the buttons. The macro should somehow know that the column the button is in, is the column to sort by.

Adding Data From Worksheet To Word Through VBA
I'm having an issue with adding data to word from excel through VBA.

I would like to add the Table to the word doc, then copy and paste data below it. The code works fine. However, after the table gets added to the top of the word doc, the rest of the stuff begins pasting from the top of the doc, causing the table to appear at the bottom...

I have (several) worksheets that have protection enabled. I have unlocked all the cells that users need access to and locked all the column and row headers/labels.

Adding & Deleting Button On Worksheet
I am trying to create two macros. The first macro will add a button, and the second macro will delete the button. I am noticing that when I add the button in the first macro, Excel is automatically naming the button (i.e. Button 26), and then when I try to delete the button with the second macro, this button name is not recognized. How can I name or set the button as a variable so that I can successfully run these two macros?

Adding Data To A Separate Worksheet
I have a userform which when you enter data and hit submit, inserts data into a seperate sheet in the same file. Code below

Private Sub CommandButton1_Click()
Sheets("Purchase Order").Select

UserForm1.tbJobNo.Value = _
Sheets("Purchase Order"). Range("I4")

UserForm1.tbJobTitle.Value = _
Sheets("Purchase Order").Range("K4")

UserForm1.tbOrderNo.Value = _
Sheets("Purchase Order").Range("I49")

UserForm1.cbdirector.Value = _
Sheets("Purchase Order").Range("K43")

Instead of placing the data into another worksheet i would like to put the data into a closed xls file named Purchase Order Database.xls.

How can adjust the code for it to do this?

A procedure residing in the "ThisWorkbook" module has just added a new worksheet into the active workbook. I now want this same procedure to write a set of code into this new sheet's code module.

Solution 1:
Create a worksheet before run-time that is already set up with the code you want behind it. Then, use this worksheet as a sort of "template" when you want to create a new worksheet as opposed to using the worksheet.add method.

Question:
I can use solution 1, but isn't there a way to insert the code on the fly?

I want to be able to have rows/cells generated with a certain text
based on the answer given by a user.

For example, My question in a cell could be "how many groups have you made?".
Now to this if the user answers "3" then I would want three cells below to
group" and so on.

Adding Two Rows After Every Existing Row
Is there any way to automatically add two new rows after every existing row containing data?

For example

1 A B C
2 D E F
3 G H I

turn into

1 A B C
(empty row)
(empty row)
2 D E F
(empty row)
(empty row)
3 G H I

i have rows of data in an accounting worksheet (payments in/out) which needs constant updating by adding new rows. within the row a formula exists in column D such as =SUM(E11:J11) and the next row is =SUM(E12:J12). if i select row 12, right click and select insert, a new blank row 12 appears but without formula in column D. is it possible to insert row with retained formula? i would prefer to add a row from a blank row below current row and have new row appear with above rows formula but this is not vital.

Adding Two Rows Together If They Have The Same Value In Column A
I've got an excel sheet which has names in column A followed by 5 numerical values in columns B-F. I'd like to get a macro that will compare the values in Column A and if they match, add the B values, the C values, the D values, the E values, and the F values and delete one row - effectively combining the row. What's the best way to go about this? Also the sheet is already in ascending alphabetical order by Column A, so the rows that need adding will be next to each other. So I guess compare each A value with the one below it and if they match add the rows, but how?

Some combination of "ctrl" with something else.

Adding A Global Name Inside A Worksheet Event
Working with named ranges is new to me, and very aggrevating to me too! I am trying to create a Named Range inside a worksheet_change event that is Global. I can create it, but it's always local to the sheet where the change event is happening.

For example:

...creates a local name instead of global one (whereas it would be global if it was created in just a normal subroutine).

Aside from using this trick: Change refersto property to workbook-level

Adding Rows And Calculation To Macro
I am extending rows on a salary schedule. As you can see below, the rows include: Bargaining Unit (GP) Schedule(2A) Range(5) Step (A) Hourly (S) and the hourly wage(11.52)

GP,2A,5,A,H,11.52
GP,2A,5,B,H,11.82
GP,2A,5,C,H,12.17
GP,2A,5,D,H,12.5
GP,2A,5,E,H,12.88
GP,2A,5,F,H,13.22
GP,2A,5,G,H,13.61
GP,2A,5,J,H,14.18
GP,2A,5,K,H,14.71
GP,2A,5,L,H,15.26
GP,2A,5,M,H,15.83

The Steps currently go from A thru M but I need to add N thru Z and after step M there is a 3.75% increase for each step.

You can see that this is a csv file (comma delimited). All of the other info needs to autofill and only the new steps (N-Z) will change and the wage for each of the new steps. The last row is 2 decimal points.

Adding Up Differnt Rows Within A Table?
My table is a much larger version of this:

A B
X1283.786
Y1684.379
Z1719.013
X10745.013
Y10860.668

i want excel to work out the total for x y and z. however i have about 100 different things x y and z could be and its a long list so an automatic way would be great.

Adding Multiple Rows With A Macro
I´ve made a simple macro in order to make easier to add new rows in a table, by filling the cells with the new data.

The problem is that according to one of the fields, I´d like that macro to add multiple rows, with exactly the same information but numbering all these rows on this specific field from 1to the number I filled on the respective cell.

For example, I have beyond another fields that I´ve already filled, the field "Parcel" and "Total of Parcels". I filled parcel with number 1 and total of parcels with 5. Then I´d like the macro to add 5 rows with the "total of parcels" cells containing 5 on every row and the field parcel filled with number 1, 2, 3, 4 and 5, for each row created.

I'm creating a spreadsheet to generate a standardized form that I use as a paralegal.

I have 2 things that I don't know how to create....

1 - I want to have y/n questions on the data entry tab. When boxes are clicked Y, it adds a row and language to the form which exists on another tab. I know how to do links and to do if then statements, but I don't know how to make it not take up space when the answer is n. Some of these clauses are pretty long so I don't want to have big blank spaces when the answer is no.

2 - I have certain sections where I need to control the number of rows. I want to have a cell on the data entry tab where I put in the number of rows that I want. The spread sheet would create those rows and I would then enter the data on the data entry tab. On the form tab, the spreadsheet would refer to the cell telling it the number of rows, add those rows, and link them to the data that I entered on the data entry tab.

Adding A Blank Row In Between Excisting Rows
This is what I have:

A-------------B
gfdfd------sjfsdnjsnd
gfdfd------sjfsdnjsnd

This is what I need:

A-------------B
gfdfd------sjfsdnjsnd
....

I just need a black row between every row that I have now. I'm sure its simple but I can't seem to figure it out. I have tried a macro but don't really know how to work them, it just made a new line in the same spot.

Extending Sum Function When Adding Rows
If I have a sum function that adds up:

= SUM(U41:U45)

but I add a row at 41 I now get sum function

=SUM(S42:S46) when I really want it to incorporate the just add row to look like this:

=SUM(U41:U46)

Adding An Auto Filter On Specific Columns Of A Worksheet..?
How do I go about using adding an auto filter on specific columns of a worksheet..?

I.e. I want to auto filter column "D", "G" and "I" but none of the columns in-between ("E", "F" and "H")

Currently I can only create the filter for one column or a group of columns that are next to each other)

Automatic Adding And Subtraction Entire Rows
What I would like to do is do a summary sheet that does a little math. I can make it add but only one row at a time. Here is the general idea...

The fields of the summary sheet are 'PromoCode', 'AdvertisingCosts', 'Labor' for example. And I want to add the currency fields (add, as in math addition) of 'AdvertisingCosts' and 'Labor'.

The fields are on another sheet. What I am hoping can be done is that all those fields for every row can be made to work without having to hard code each one.

Also I would like to have it update with any NEW rows that arise. Like if the existing rows were 'row1' and 'row2' and then some other time 'row3' is added that 'row3' automatically gets included on the summary sheet.

Adding Numbers To Rows Using A Command Button ()
I have a user form with combo boxes. The user selects items from the combo box and when they click the command button the selection is written to the spreadsheet. Each time the button is clicked it adds an item to the sheet making a list of items. I am trying to assign a number to each item when the command button is clicked so I have numbered list. Here is my code so far:

Dim RowCount As String

'Writes user inputs to Active Worksheet when Add Item Button is clicked.

RowCount = Worksheets("Sheet1").Range

("A1").CurrentRegion.Rows.Count

With Worksheets("Sheet1").Range("A1")

.Offset(RowCount, 0).Value = ' This is where I have been trying to code in the numbering

.Offset(RowCount, 1).Value = Me.Combobox1

.Offset(RowCount, 2).Value = Me.ComboBox2

End With

End Sub

Adding Unique Identifier To Rows Of Data
I am trying to use a formula that will populate A3 with the value of A2, all the way down the column. (as per the area highlighted in yellow) As you can see on the example sheet, the account code changes periodically, this has been causing me many problems!

As the list i am working on has around 100 differfent codes, it is too time consuming to copy paste manually

Reason for this is so that each invoice number in column B, will have a unique identifer in column A (account code)

Process Of Adding More Rows Has Slowed Down Considerably
I am building an inventory list for an online store with Excel 2002. Everything was fine until I reached 10,000 rows and 50 columns.

I use formulas to convert wholesale price to retail price on about ten columns.

Overnight the process of adding more rows has slowed down considerably.

I had my computer checked out and they say it is working just fine.

Adding Multiple Rows Of Text In A Msgbox.!
I believe this would be a very simple query for some but I am a complete novice in VBa and am using a msgbox in one of my file but I need to split the message text into multiple lines but I do not know how to do the same.

Run Time Error 1004 When Adding Rows
Run time error 1004 when adding rows. I have the following code, called from a Userform:

Restrict The Adding The Rows And Columns And Also Deletion
I want to restrict the adding the rows and columns and also deletion of rows and columns in excel template, the same template is circulated to all the program owners to capture there project metrics. With that template through macro values are extracting to consolidated report. Some times program owners are adding extra columns/rows with that we are getting errors/incorrect reports.

Conditional Formating - Adding & Deleting Rows
I am using several formulas to conditionally format and fill in rows from column A to column BS. These are the formulas and application areas:

=\$BS4<>"" Applied to =\$A\$3:BS\$350
=\$G3<>"" Applied to =\$A\$3:BS\$350
=OR(\$J3="Regional Manager",\$J3="Assistant Manager",\$J3="Manager") Applied to =\$A\$3:BS\$350
=OR(\$J3="Recruiting Manager",\$J3="Owner",\$J3="District Manager",\$J3="Office Manager") Applied to =\$A\$3:BS\$350

If I do not add or delete any rows, everything works just fine. The problem I am running into is that this list is constantly updated and rows are being added and deleted. When I do this, it duplicates my rules and changes my applied to ranges. Here are some examples of the changes:

=\$N\$3:\$BS\$3
=\$A\$3:\$M\$63
=\$N\$4:\$BS\$64

This creates much excess work, when I have to go back and change everything back to the way it should be. how I can either modify my formulas or application areas to correct this situation?

Worksheet Name To Variable
AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".

Sitenameshort = "_Bmth"

ShtName1 = "Weekly" & Sitenameshort

shtName = ShtName1. Name 'This line doesnt work

Set Nws = Sheets(shtName)

It Errors on the marked line.

Use Of Worksheet Variable
I'm having difficulty using a worksheet variable with control objects on worksheets.
The following code is successful at getting the value of the text in textbox txtDCN.

Public Sub test()
Dim text As String
text = Worksheets("Master Table").txtDCN.Value
End Sub

The following code gives compile error "Method or data member not found" on the last line of code. I've searched around a bit all over the web but cannot find any examples of this same error. It's obvious that the compiler cannot figure out the reference to the text box but I cannot figure out why for the life of me.

Public Sub test()
Dim ws As Worksheet
Dim text As String
Set ws = Worksheets("Master Table")
text = ws.txtDCN.Value
End Sub

Deleting And Adding Rows Based On Date Criteria
ColumnA in my data base contains dates. How can I delete the entire row if the date is before today's date and add a new date at the end of the range to replace the deleted row.

Adding Up Rows Of Text/numbers That Are Exact Matchs
Need code for adding up rows of cells of text mixed with numbers, each row would look like

b1230 10 7/8 Gables 3/4 Pref Birch
b2230 10 7/8 Gables 3/4 Pref Birch
b3230 10 7/8 Gables 3/4 Pref Birch
b4230 10 7/8 Gables 3/4 Pref Birch
b1138 1/2 10 7/8 Base 3/4 Pref Birch
b2138 1/2 10 7/8 Base 3/4 Pref Birch
b3138 1/2 10 7/8 Base 3/4 Pref Birch
b4128 1/2 10 7/8 Base 3/4 Pref Birch

and i would like to make them add up to look like this

b1, b2, b3, b4 8 30 10 7/8 Gables 3/4 Pref Birch
b1, b2, b3 3 38 1/2 10 7/8 Base 3/4 Pref Birch
b4 1 38 1/2 10 7/8 Base 3/4 Pref Birch

I've managed to use some code I found to add a new row below the selected row, and duplicate all the forumlas of the source row. It worked fine dozens of times yesterday, but today it's decided not to work. It gets as far as creating the new row(s), but then just hangs & excel crashes before duplicating the formulas. I've even tried reverting to an earlier version, which also worked fine, but this crashes also!

' Unlock Worksheet
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line
Dim sht As Worksheet, shts() As String, i As Integer
Redim shts(1 To Worksheets.Application. ActiveWorkbook. _
Windows(1).SelectedSheets.Count)..................................

Fixed Range Name When Adding Rows/Columns/Cells
How do I define a " Range Name" that is fixed? So if I insert a row above the range it has no effect on the orginal range.

IE: Range Name "MyRange" = A10:A100

I insert a new row at A4 then "MyRange" = A11:A101

I want it to stay at A10:A100.

Combining/merging Multiple Rows From One Worksheet Into One Cell In Another Worksheet
I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.

This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.

I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.

I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.

Formatting Worksheet: Re-adjust The Worksheet To Make It More User Friendly Based On Number Of Rows In Current Sheet
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.

Using Variable Cell Value As Worksheet Name
I have a macro on a button click that copies and pastes the template worksheet into the same workbook on a dialy basis, i would like to rename the "template" copied file to a unique worksheet name based on a date ie 31_3 (31st march) and so on, the cell that provides the date changes on a daily basis to the next one, however when the script runs it uses the original macro recording date rather than the new one!, the changable cell is AS2.

Replacing Worksheet;with Variable Name
I'm replacing a specific sheet on a workbook with another one, but I want the users to be able to browse to a file, replace the sheet with the new one and re-establish the links on the replaced sheet with the target workbook. This macro is running out of the workbook that supplies the replacement sheet. The idea is that the user opens this workbook, runs the macro, browses to the file they need to fix and hey presto! I'm doing something stupid with my variable name, I'm sure it's syntax but I don't know what I'm doing wrong. The code is posted below.

Sub Macro1()
Dim ToBeFixed
ToBeFixed = Application.GetOpenFilename
Workbooks.Open (ToBeFixed)
Sheets("!").Visible = True
Sheets("!").Select
ActiveWindow.SelectedSheets.Delete.....................

Set Variable To Worksheet In Another Workbook
In conjuction with a previous request I've tried to create my own vba code (actually amend existing code i have) but its failing........ whats wrong??

Sub Retail_1_Xfer()
Dim NextRow As Long, Isht As Worksheet, Lsht As Worksheet
Application. ScreenUpdating = False
Set Isht = Worksheets("Retail Team 1")
Set Lsht = Worksheets("Raw Data")
NextRow = Lsht. Cells(Rows.Count, 2).End(xlUp).Row + 1 'next empty row in col A
Lsht.Range("A" & NextRow).Value = Isht.Range("A9").Value
Lsht.Range("B" & NextRow).Value = Isht.Range("A4").Value
Lsht.Range("C" & NextRow).Value = Isht.Range("B9").Value
Lsht.Range("D" & NextRow).Value = Isht.Range("N3").Value
Lsht.Range("E" & NextRow).Value = Isht.Range("N2").Value
Lsht.Range("F" & NextRow).Value = Isht.Range("BJ9").Value
End Sub

I think its got something to do with the fact that the worksheet " Retail Team 1 " is in another workbook

Adding Rows With Control Number That Only Repeat 3 Times: Formula
I'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.

Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.

So I need something that can take each control number that has three cycles and add their # of 0:15 together.

The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.

Ultimately I would like the results on a separate sheet.

This is only a part of the file, there are over 1000 different control numbers.

Lock Cell From Moving When Adding/Deleting Rows Or Columns
Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?

Prevent Custom Function Running When Inserting/Adding Rows
I have a piece of code that hides unneeded rows, it does work but it is very slow.
This is in the worksheet part of the project. My problem is as it runs it "jumps" in to a function I have in a module that counts continuous rows. I would like to know why it is doing this and what I need to do to stop it? I have another piece of code that is structered exactly the same that hides unneeded columns and I do not have any problems with that. I know both pieces of code are dealing with rows and I think this is part of the problem but I can not see any reason why the first bit would call the second bit?

Dim C
With Worksheets("CEN OAS"). Range("D5:D378")
.EntireRow.Hidden = False
End With
For Each C In Worksheets("CEN OAS").Range("D5:D378")
If C.Value = "" Then
C.EntireRow.Hidden = True
End If
Next C

Function to count used rows....................