Insert Or Delete Row Using Macro

Oct 30, 2009

I have columns 1 to 5 that requires user input. At the beginning of first row, i have created two buttons +R to insert row and -R to delete row. I used macro recording to get this done. It works well but I need both buttons to be copied on for other rows as well. Let me re-explain, when a person clicks on +R in row 10 I need a new row inserted at row 11 which both the buttons +R and -R copied in. When a person clicks on -R at row 10, i want row 10 to be deleted. the first default should never be deleted. All rows should be added before statistic as statistic row will calculate all the inputs from user start to finish. Can this be done?

123456+R

-R
Statistic: PLS refer to this diagram

12345+R,- R

Statistic: ignore this diagram
I even tried http://www.mvps.org/dmcritchie/excel/insrtrow.htm but it is not working. I am not sure where I am doing wrong.

View 9 Replies


ADVERTISEMENT

Macro Button - Insert / Delete Across Multiple Worksheets?

Mar 19, 2014

I'm trying to track information pertaining to employees across different worksheets in one excel file. I have one sheet that is the master list of employees. The first column of every worksheet is the same (employees names based on their location) but track different information.

I want to be able to create a macro button that will update all the worksheets if i insert or delete an employee from the master list. When a new row is inserted, the other worksheets should be updated as well with the new name and a blank row to be filled in. And when a name is deleted, the entire row should be deleted as well.

View 2 Replies View Related

Macro To Extract/insert Data Then Delete Rows

Jul 25, 2008

I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.

******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3  Transactions for  CUSTOMER ID: 0000CUST7   Sales        4        5        6Product summary for Vehicle ID   00001080       7        8Product Description  Transactions Quantity9        101 Unleaded  3 57.60  GL11        12Hose summary for Vehicle ID 00001080       13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60  GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


I am trying to get the data into a more convenient format for analysis. I need a macro that will:

1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).

2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.

******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded  3 57.60  GL111081 Unleaded  6 84.70  GL121122 Unleaded  5 47.00  GL131182 Unleaded  8 95.80  GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

VBA Macro To Delete Rows/Columns, Insert Formulas & Auto Fill

May 28, 2009

I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.

Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"

UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................

View 2 Replies View Related

Insert / Delete Rows

Jul 6, 2009

I'd like to create a variable list. If a number is entered into B7 (see attachement), I would like that number of rows being inserted below. Starting at A8. But I would like the list to be variable. So if a lower number is entered, the rows would be deleted. And I would be great if the formulas could be maintained.

View 5 Replies View Related

Insert And Delete Rows With VBA

Jun 6, 2014

I've a protected sheet with one table with 6 columns and 1 row (to be filled), and the column headers.

I want to allow the user that will fill the sheet to insert more rows with a button (+) I want also to allow him to delete the last row with a button (-), except the first row, because the table must have at least one row.

How can I do this with vba code?

View 1 Replies View Related

Insert / Delete Row Causes Error In VBA Code?

Mar 15, 2013

I need to change the colour of cells depending on contents. The following code works perfectly until I try to insert or delete a row in the worksheet. Then I get Run-time error 13, Type mismatch.

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Set I = Intersect(Target, Range("E5:E100"))
If Not I Is Nothing Then

[Code]....

View 2 Replies View Related

Able To Delete And Insert But Still Be Able To View The Sum Of A7:A39 At All Times..

Jan 1, 2009

I need to work out a total of column A7:A39. Even with my lack of excel knowledge, I can do that with the sum formula. But, each week, I need to delete the values in A39 by deleting that row, and inserting a row into A7 and putting different data in.

I have my $ signs in place to make sure the formula relates to that column only, but when I insert the row at A7, the formula moves to A8:A39 not A7:A39. Basically I need to be able to delete and insert but still be able to view the sum of A7:A39 at all times.

View 4 Replies View Related

Duplicating Insert Row And Delete Row On Another Sheet

Sep 27, 2009

I have two sheets, they basically contain shifts. They are laid out identically the only thing that changes is the date along row 1. In order not to have to recreate the workers from sheet1 onto sheet2 I use the following

View 2 Replies View Related

VBA Delete Rows And Insert Formulas

Jun 23, 2013

delete rows of a report which contains $- or zero value but not row with Beginning Balance. Data begins from Column B to Column E. Some records may need to be deleted completely from Account Number down to Subtotal rows plus one empty row, if the Subtotal amount is $- or zero. Below is the sample data and how the finished sheet should look like. The report will comes in thousand of line. I don't have knowledge in programing but believe it can be done through VBA.

Account Number

211-00-5936-00-001-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount

[code]....

if Subtotal is $0, then delete the row starting from Account Number to Subtotal plus one empty row

211-00-5936-00-002-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount

[code]....

View 2 Replies View Related

VBA To Insert And Delete Multiple Rows

May 30, 2009

Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete.

View 9 Replies View Related

Checkbox To Delete Or Insert Rows

Mar 23, 2007

I have a worksheet that has been created to essentially request information needed to determine a demo system configuration. Within this worksheet there are a series of checkboxes that if checked, will need to have the form extended (i.e. add additional questions to clarify say a model or manufacturer). For example, if the Yes checkbox is checked next to a question I want a macro to copy some rows from a hidden worksheet within the same workbook to the bottom of the non-hidden worksheet. If the No checkbox is clicked, nothing should happen. Furthermore, if a user accidently click Yes but didn't mean to I want the added rows to be removed. This is further complicated by the fact that there are 2 or 3 questions that have check boxes that can add additional questions.

View 9 Replies View Related

Macro Delete Page (delete A Sheet In A Macro But When I Run The Macro)

Jun 19, 2009

I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.

Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete

View 4 Replies View Related

Excel And VBA - Insert Group Of Cells Or Delete Them?

Apr 12, 2013

I am working on a contact information form for given relationships, and I'm trying to figure out the best way to design a button that copies the "form" (not an actual VBA form but a grouping of formated cells for information collection) and inserts it into the document. I would also like a button that deletes unused and empty forms so that we only print forms that are filled in.

I tried recording a macro to do it, but it's messy and creates issues with hidden rows and pagination.

View 4 Replies View Related

Identify Button (shape) Row To Insert Row And Delete Row

Feb 3, 2009

I have a button (group containing and add and delete button).

I want to identify the row (position of shape/button calling the macro) to enable inserting a new row (1 row down from current row).

Then do the same to delete a row (position of shape/button calling the macro) to enable deletion of selected row.

This will allow me to add/insert rows by the button located at that row

The problem i have is getting the row property (row position of the button eg. TopLeftCell.Row) of the add button. The add button (RowBtnAdd) is a shape within a group (BtnGrp)

I also note that when a group is copied, it has the same shape name as that copied.
I want to keep the add and delete shape within the group (BtnGrp).
I do not want to select a cell or row or enter a row number to delete etc.

refer to sample workbook attached. Currently only has one record row.

View 6 Replies View Related

Using VBA And If Statements To Delete / Shift Up And Insert Cells

Sep 7, 2012

I am trying to create a macro that will add and delete individual cells depending on an IF statement. The code that I created deletes/adds cells regardless of the IF statement.

If Sheet1.Range("F3").Value = Sheet1.Range("G2").Value Then
Range("G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End

ElseIf Sheet1.Range("F3").Value = Sheet1.Range("G4").Value Then
Range("G3").Delete Shift:=xlUp
End
End If

Also, what is the easiest way to have this code apply to all rows within column F, while adjusting the corresponding cells referenced in column G? The last row within the spreadsheet changes.

View 3 Replies View Related

Formula References Not Updating On Row Insert/Delete

Jul 26, 2007

I have a worksheet with many different formulas in many different cells. When I insert or delete a row, there is one formula in one cell that does update to reflect the change in rows. The cell is located several rows below the section where rows are changing. It is a simple formula too. Here is the formula: "= SUM($E$3:E11)*0.09". If I delete more rows that cause the highest row number to be less than 11, this formula updates. If I insert more rows and go beyond row 11, the formula does not update. I have tried using $E$11, $E11, and E$11 to no avail.

View 9 Replies View Related

Compare Columns & Insert / Delete Rows

Feb 22, 2008

I have a large data file. In column A is 8760 entries (every hour of every day of the year). Columns D & E are linked, Column D is the time and date whilst Column E is a parameter assocaied with that time and date.

There should also be 8760 entries in these columns, however there are some missing data lines in columns D & E. I would therefore like to move the rows in columns D & E so they match with the entries in column A. Can anyone help? I have a headache as i have over 70 files to complete this operation on.

View 9 Replies View Related

Allow Users To Insert / Delete Rows On A Protected Worksheet?

Apr 5, 2014

I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???

I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.

View 2 Replies View Related

Insert / Delete A Column - Copy Formula And Apply To Other Worksheet

Jul 19, 2013

I need to do something to my workbook, and I need to do this task:

When I insert/delete a column between E & F in sheet 1, the formula (not the value) in the column E was applied too to the new column I've inserted/deleted..

Then, when that happened to the sheet 1, It would happen too to the other sheet automatically..

So I don't need to insert/delete the row and copy the formula manually for each worksheet..

I know that I could simply solve it with grouping the sheet tab..

But I have plenty of data that needed to be inserted and applied with the formula..

I will attach the little example : insert.xlsx

And one more thing, I received this VB code from [URL] ..... for inserting the column:

VB:
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True

[Code] .....

And this code for deleting the column:

VB:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True

[Code] .....

View 9 Replies View Related

Insert Rows Based On User Input But Then Auto Delete Them At The End

May 25, 2013

I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.

The code I have for the inserting the rows (from searching through forums) is:

Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9

[code]....

View 1 Replies View Related

Excel 2010 :: Unable To Right Click For Format Cells - Insert Or Delete

Sep 27, 2011

Just recently My Excel 2010 has decided to not let me right click to format cells, delete or insert rows. I can do these functions from the ribbon, but not via right click. this happens in both existing spreadsheets where I am the author, or even a brand new spreadsheet like in the image below. we have restarted the computer, Uninstall and reinstalled Office and still get same symptoms. I got here thru google but cannot find an answer anywhere.

I am very computer literate and even our IT personnel have looked at this with no answer. as you can see in the image, these options are greyed out.

Running Office standard 2010, Windows 7 pro x64

View 8 Replies View Related

Delete Columns, Increment Alphanumeric Text, Insert Rows & Sort

Feb 18, 2008

I am having to write a vba code for a survey data captured code online.

I am attaching the code and the vba module that is suupose to do

following things

2) dELETES THE LAST 3 COLUMNS

3) Initial 6 letters from each cell (Q0001=) UPTO (Q00011=)

4)Inserts new row at top from Q1 TO Q11

5) Sorts data on Q6 (Faculty/Staff)

Before all this the i need to insert blank cells for questions that have not been answered and shift the cells right (condition 1) ....

View 9 Replies View Related

Macro Recording 2007: Record A Macro To Insert An Autoshape

Feb 18, 2007

I recently installed Excel 2007, and have shared others' joy in searching for things on the ribbon. I tried to record a macro to insert an autoshape. Excel creates the macro, but drawing the autoshape is not recorded. I'm sure I'm missing something obvious ...

View 7 Replies View Related

Insert Row Macro

Dec 30, 2008

I would like to find the code to make a button in excel, that when clicked, will insert a new row into the last row of a section so that the formatting is the same as a sample of other rows in that section, but it will be clear of data and only contain the correct formatting. I would also like this code to make a sum formula in a cell below the newly inserted row to change and to include the new cell within its formula.

For example:

I have a form on excel that has space for 6 rows of data with 3 pieces of information (which are: item, section, cost). If / when I need to add a 7th line I have box that I would like to assign a macro to so that when I click on it, it adds a 7th line with the same formatting that can be found in rows 1-6 but will be void of data. However, under the 6th row I have a total of the 'cost' with an '=sum(D7:D12)' formula and any other code I have found and have used it does not automatically tell the formula to add an additional cell to its summation to make it =sum(D7:D13).

View 9 Replies View Related

Macro: Insert Sum

May 13, 2009

I'm working on this sheet where I need to first, sort the invoices by the due date. separate due and not due. for example, due date is 05/05/09 (pls see sheet 2). a blank row is then inserted (above the undue invoices) with "total due to pay" then the sum for due invoices.

for the invoices not due, i need to sum those also and finally, the total of the two figures at the very bottom. I need a macro code to run when the list contains hundreds of invoices.

View 4 Replies View Related

Insert Row Using Macro

Nov 27, 2009

I hav a macro to insert a new row. But i also want the macro to ask the user to input where he wants(which row number) to insert the new row??

For example when i run the macro, an inputbox should come up asking to input the row number. The user should be able to enter the row number (take 10 for now). And Excel should automatically insert a new row in row number 10. So the previous row 10 now becomes row 11.

View 9 Replies View Related

Run Macro Upon Row Insert

Apr 11, 2007

I feel like this is textbook to the point of being in FAQ but couldnt find it, so if the answer indeed exists in FAQ - notify me of the url and i will refer to that instead. But my question is fairly simple, I'd like to have a macro run upon the insertion of a row.

View 3 Replies View Related

Macro To Insert A New Row

Aug 14, 2008

how to automaticly create a new row below the last data entry. I have a list of items with relevant data in each row. But below all the data I have statistics of total percentages of certain things. I want to keep that at the bottom of the list. When I add another row for more information I want those statistics to jump down a row so I have more blank rows to add in more data. Is there an easy way to do that?

Also something kinda related. I know you can freeze rows and columbs at the top and left of the spread sheet, is there any way I can freeze those statistics at the bottom so they are always visable but the data on top of it can scroll through? I dont want to split the page cause that kinda causes more problems.

View 8 Replies View Related

Macro To Delete Vba Code - Macro Security Warning

Feb 7, 2008

I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.

My worksheet runs a lot of code, then deletes all macro code and saves itself.

The problem is the next time i open I still get the macro security warning!

I've checked thoroughly and there is definatley no vba left anywhere.

If I open the document, enable macros, and save it, then open it again, I no longer get the warning.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved