Adding And Deleting Multiple Rows

Jul 9, 2013

I have a spreadsheet with multiple similar entries I would like to add up. What would work well for me is to be able to input a column like a and a target column like e.

So in this case it would search throughout column a and wherever it finds a duplicate entry add column e and then delete one of the rows. I could then run it again under different column criteria if needed.

So if I had the below sheet:

I could search by column a and add up column h to read like this:

View 1 Replies


ADVERTISEMENT

Conditional Formating - Adding & Deleting Rows

Nov 9, 2008

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?

View 4 Replies View Related

Automatically Adjusting Sum Of Numbers When Adding / Deleting Rows

Jul 21, 2014

I currently have this formula to deal with this:

=SUM(J5:INDEX(J:J,ROW()-1))

However, whenever I delete the top row...I get a reference error and have to re-adjust...

View 7 Replies View Related

Adding And Deleting Rows In Worksheet While Maintaining Formulas?

Mar 26, 2014

I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.

I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:

1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table

The code I'm using for my "Add" button is:

[Code].....

The code I'm using for my "Delete" button is:

[Code] .....
The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above).

Attached File : Productivity Report (HH).xlsm‎

View 8 Replies View Related

Automatically Adjusting Sum Of Numbers When Adding / Deleting Rows?

Jul 21, 2014

I currently have this formula to deal with this:

=SUM(J5:INDEX(J:J,ROW()-1))

However, whenever I delete the top row...I get a reference error and have to re-adjust...

View 9 Replies View Related

Deleting And Adding Rows Based On Date Criteria

May 21, 2006

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.

View 9 Replies View Related

Lock Cell From Moving When Adding/Deleting Rows Or Columns

Jun 16, 2008

Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?

View 6 Replies View Related

Excel 2007 :: Adding And Deleting Rows With Hide Row Macro CheckBox?

Mar 18, 2014

I currently have the following Macro for one of my many checkboxes in 2007 Excel:

[Code] .....

It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?

View 1 Replies View Related

Conditional Formatting: Reference Changes Adding/Deleting Rows/Columns Or Cells

Sep 26, 2006

I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.

View 3 Replies View Related

Deleting Multiple Rows?

Mar 15, 2013

I have a large list of coordinates that I pasted from a website, and in Excel it has an emty row between every coordinate. I have about 2,000 cordinates, so ~4,000 rows. Is there a way to delete every other row besides manually?

View 1 Replies View Related

Deleting Multiple Rows?

Jan 18, 2013

In my spreadsheet, column A can contain the same word over several rows. If column B is blank at any time - I want to delete all rows that have the same word in column A.

Apple 1
Orange 2
Apple
Peach 1
Peach 2

So in this example - I want to delete rows 1 and 3 that show the word Apple.

View 2 Replies View Related

Deleting Multiple Rows With Multiple Criteria

Feb 6, 2007

I have a need to delete multiple rows with multiple criteria; criteria contains about 400 different and unique entries that have to be deleted. The code I obtained from this forum was modified but only deletes some entries and bypasses others. Row 1 is header and column 1 contains criteria that it it meets the " Case" it is supposed to be deleted. Example of code is below the Case has been shorted, for posting but normally each case runs the maximum length allowed. Please let me know where I messing up, I am thinking the it needs to be looping until all entries are deleted but I don't know how.

Sub DeleteRecords()
Dim n As Long, lastrow As Long
Dim ty As String
Dim a As Integer
lastrow = Range("D65536").End(xlUp).Row
a = 1 'this is the column in which you will find the notation finance numbers (Col X = 24)
For n = lastrow To 2 Step -1
ty = UCase(Cells(n, a))
Select Case ty..........................

View 2 Replies View Related

Deleting Rows NOT EQUAL To Multiple Criterion

Apr 28, 2008

I have a spreadsheet with several thousand lines on it and I only want certain rows from it. Unfortunately I cannot use a sort as I have to keep groups of data in the same order (the line count in between the data that I need left behind isn't always the same so I can't go that route either). I was hoping there was some code to delete all rows other than those where the cell in column A contains either "NAME"; "Data1" or "Data 2".

I tried the code below but that didn't work.

View 10 Replies View Related

Deleting Rows Based On Multiple Filter Options?

Aug 3, 2012

I need to review a 400,000 row spreadsheet and remove all records where 5 columns are populated with data so I am only eft with records that are missing information.

View 9 Replies View Related

Deleting Rows Where Cells In Multiple Columns Are Blank

Apr 17, 2013

I work with a spreadsheet every week to input values and subtotal them. These values change constantly and instead of going through and manually deleting each row in a 100+ row spreadsheet to be able to import into another program, I'm looking for an quicker way to keep my data in order but consolidate by getting rid of only the rows where both column A and B are blank.

Here's an example of what I'm working with:

5
10
15
20
50
2
4
6
8
20

This is what I need the final product to look like:

5
10
15
20
50
2
4
6
8
20

So I would like to quickly delete rows 2, 5, and 11. All of the other answers I've found only show how to delete rows based on empty cells in only 1 column. How can I quickly delete the rows where both columns are empty?

View 5 Replies View Related

Deleting Multiple Non Contiguous Rows From A TABLE Based On Selection In A Listbox?

Feb 6, 2013

I have two pieces of code that perform virtually identical functions on different sets of data.Basically, the code deletes rows of data based on a selection in a listbox. One code works perfect, the other has an issue.

The Issue:

When I select a single row for deletion, or multiple contiguous rows, everything works fine. It is when multiple Non-Contiguous rows are selected that the problem occurs. I get a "Delete Method of Range Class" error.

The data for the code that doesn't work is in a defined TABLE, the date for the code that does work isn't. So I assume this is where the problem lies.

I converted the data for the code that did not work from a table to a range, and everything works fine. However, I would really prefer that this data remain a table. There is consistently going to be rows added and deleted, and a table will take those adjustments into account, whereas a named range shrinks each time I delete a row, and doesn't grow as I add rows.Here is the code that works:

VB:
'Delete Selected Offers
Else
For r = 0 To ListBox1.ListCount - 1 [code].....

View 6 Replies View Related

Excel 2003 :: Deleting Rows For Multiple Files In Separate Workbook

Sep 10, 2012

I'm new to this forum and to VBA

First-off, I'm using Excel 2003 SP3.

The setup: A software application I regularly use produces csv data files (in this case approx 300 files). These I have converted to xls format using a separate macro. The files are in one folder and named as follows eg

G1 18800.xls
G1 18802.xls
G2 18975.xls
G3 19881.xls
G3 19990.xls
G5 19990.xls
etc

The files contain the following data:

DATE TIME VALUE STEPS EXCL ACCEL
01-Oct-03 08:00 0 0 FALSE 0
01-Oct-03 08:01 0 0 FALSE 0
01-Oct-03 08:02 0 0 FALSE 0
01-Oct-03 08:03 0 0 FALSE 0
01-Oct-03 08:04 757 20 FALSE 0.18
01-Oct-03 08:05 1714 32 FALSE 0.44
01-Oct-03 08:06 1524 32 FALSE 0.39
01-Oct-03 08:07 1665 45 FALSE 0.47
01-Oct-03 08:08 1644 42 FALSE 0.46
01-Oct-03 08:09 263 8 FALSE 0.06
etc

I then created a macro using code I sourced from the internet, and included some addtional commands (filename, copy/paste). See below. This macro opens the all the xls data files in the folder and copies the relevant data to an analysis workbook [Analysis sheet, Results sheet, Master sheet] ie the data is copied from the data file and pasted into the Analysis worksheet. Then the results are copied from the Results sheet to the Master sheet. So far so everything works.

My problem is this: I'm stumped at how to delete rows from the xls data files before running the analysis workbook and macro. In other words, after converting the csv files to xls format I need to clean the xls data files.

I have a separate xls file with criteria data in two columns:

FileName Date
G1 18800 06-Oct-03
G1 18801 02-Oct-03
G1 18801 03-Oct-03
G1 18801 05-Oct-03
G2 18795 14-Oct-03
G2 18795 15-Oct-03
G2 18795 16-Oct-03
G2 18795 17-Oct-03
etc

The rows in a particular data file, with dates that are not present in the Criteria workbook must be deleted. This to be done for every data file in the folder. Also, I need to exclude rows for specific time periods eg 12H00AM - 04H00AM from all the files irrespective of date.

the code needed [should a separate macro be run or can code be placed within the present macro?]. I've tried looking for something similar on the internet, but my requirements seem too customized to be able to adapt the code that I found. And of course this is waaaay above my present skill level!

Option Explicit

Sub CopyPaste()

'This code opens up data files (xls) in a specified folder and copies data A1:G17281 to an Analysis workbook (Analysis sheet).

'The data from the Results sheet is then copied to the Master sheet.

Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim MyPath As String
Dim MyFile As String

[Code]...

View 9 Replies View Related

Formula For Adding 12.5% Onto Multiple Rows?

Apr 25, 2014

I have a quotation that I am working on that I need to add 12.5% onto each seperate line price.

There are 300 individual lines on the quote all with different pricing but they all need 12.5% adding on so I am hoping there is a simple way to do a bulk sum for all of the rows rather than having to go through each row and creating a sum for each. - ie =(K28*1.125)

View 3 Replies View Related

Adding Multiple Rows With A Macro

Feb 23, 2010

Ive 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, Id 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 Ive already filled, the field "Parcel" and "Total of Parcels". I filled parcel with number 1 and total of parcels with 5. Then Id 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.

View 14 Replies View Related

Adding Multiple Rows Of Text In A Msgbox.!

Oct 7, 2009

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.

View 6 Replies View Related

Adding Multiple Rows Using Insert Copied Cells Function?

Feb 26, 2013

I have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.

I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.

View 4 Replies View Related

Adding And Deleting Pictures?

Feb 11, 2013

I am working on a worksheet where users are asked by a userform combo box to select a product. When they select the product a picture of that product appears in the top left corner of the worksheet.

I have managed to get the picture to appear however delete any pictures that were in that cell before pasting a new one, cause when changes the product a picture is pasted over on top of the previous one however if the pictures are not the same size you may see the previous under the current.

delete any previous pictures. Here is the code I have so far.

Sub GetPicture()
Set Rng = Sheets("Products").Range("A2", Sheets("Products").Range("A2").End(xlDown)) '.Resize(, 10))

[Code].....

View 3 Replies View Related

Adding & Deleting Button On Worksheet

Oct 24, 2006

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?

View 6 Replies View Related

Adding And Deleting Columns, Ruins Code

Jul 21, 2008

I have a spreadsheet with 20 or so columns but that number can change at any time. I need my code to by dynamic so if a column is deleted (or added) my code will still work.

Each column has a name in row 2 and I'm trying to use that in order to make everything dynamic. For example, my code counts the number of blue cells in colums D4:G68, but now if someone adds a column before column D, everything gets messed up and it will still count the cells in D4:G68 but I want to use E4:H64 now. Does that make sense? I have made code that searches the column names and returns the column number to correspond to the name. How would I change this?

Range ("C8") = "= CountBlue(E4:G68)"

the code doesn't look exactly like this, I believe it is actually in R1C1 format

View 9 Replies View Related

Code To Stop A User Adding Or Deleting Sheets

Aug 31, 2007

I need to stop users from adding and deleting sheets in a workbook

My idea was obviously to disable the command bars to add or delete sheets in open event and then put back in before close.

But then i thought...whats stopping them from right clicking the sheet tab and inserting a sheet, can i remove that menu to...?

I also wasnt sure if there were short cut keys to add or delete sheets.?

Some of my users use excel alot so i want to account for an tips they know that i might not.

View 9 Replies View Related

Excel 2010 :: Delete Filtered Rows Without Deleting The Hidden Rows?

Sep 25, 2012

How do I delete filtered rows without deleting the hidden rows in excel 2010?

View 8 Replies View Related

Clear Rows Vs. Deleting Rows To Avoid #REF! Formula Error - Macro

Jun 9, 2009

I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).

Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.

Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to

View 9 Replies View Related

Code For Deleting Rows Verses Hiding Rows

May 25, 2013

The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.

Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell

Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell

View 4 Replies View Related

Delete Blank Rows (formula Not Deleting All Rows)

Sep 30, 2008

I have the following codes to delete all blank rows in column A

Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow

With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With

End Sub

Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.

View 9 Replies View Related

Deleting Rows Macro :: Rows That Don't Contain Word ...

Jan 9, 2008

I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"

My code if needed is:


Sub Find_details()
Dim rng As Range
Dim what As String
what = "DETAILS"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub

View 9 Replies View Related







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