Deleting Rows Without Modifying Formulas
Apr 9, 2009
I have a large amount of data on a sheet which is being looked at by the following formula
{=AVERAGE(IF(LEFT(RAW_DATA!$C$4:$C$10000,LEN(Dashboard!$I$10))=Dashboard!$I$10,IF(RAW_DATA!$A$4:$A$10000=$A2,RAW_DATA!$J $4:$J$10000)))}
However, i don't need to keep the oldest data so I want to write a macro that deletes some of the top rows. Doing this in the normal way causes the formula above to change. I.e deleting one row cause the range to become $J$4:$J$9999 and so on.
Is there any way of deleting a row without affecting the formulas that are looking at it?
View 9 Replies
ADVERTISEMENT
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
Dec 12, 2013
Whenever you insert / delete row or cut cells out in Mon/Tue sheets it's causing issues in the table in the Weekly view tab.
Is there any way to prevent it? I don't mind redoing whole spreadsheet, it was done ages ago in a very fast manner.
I gave you an idea by deleting a row range in Mon tab which causes #REF error.
View 9 Replies
View Related
May 24, 2008
I am trying to modify the below code in order for it to:
1) Check the cells in Column A to find any blank cells and zero values. If the macro finds a blank cell or a zero then delete the entire row.
2) Check the cells in Column D for any blank cells and "N/A" (this N/A is text, it is not an excel error). If the macro finds a blank cell or "N/A" then delete the entire row.
Here is the old code
Sub DL()
Dim lastrow As Long, i As Long
With ActiveSheet
On Error Resume Next
.Columns(3).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
lastrow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = lastrow To 1 Step -1
If .Range("C" & i).Value = 0 And .Range("D" & i).Value = 0 Then .Rows(i).Delete
If .Range("C" & i).Value = "N/A" Then .Rows(i).Delete
Next i
End With
End Sub
Here is the modified code...
Sub DL()
Dim lastrow As Long, i As Long
With ActiveSheet
On Error Resume Next
.Columns(3).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
lastrow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = lastrow To 1 Step -1
If .Range("A" & i).Value = 0 And .Range("A" & i).Value = "" Then .Rows(i).Delete
If .Range("D" & i).Value = "" and .Rang("D" & i).Value ="N/A" Then .Rows(i).Delete
Next i
End With
End Sub
1) Is this the correct and efficient way to accomplish the task?
2) How to i change this in order to select a specific sheet?
View 9 Replies
View Related
Sep 4, 2006
I have a macro that imports a text file. I then have summary sheets that reference to the sheets with the imported text file details. This is a task I complete every month.
The next month I delete the sheets with the imported text file details before importing current month's details. But when I do that it screws up my references.
Here's an example of a reference with previous months' imported text file details:
= SUM('X'!$I$1:$I$3000)/2+SUM('Y'!$I$1:$I$3000)/2
When I delete the sheets with the imported text file details and import current month's details, my reference suddenly looks like this:
=SUM(#REF!$I$1:$I$3000)/2+SUM(#REF!$I$1:$I$3000)/2
What can I do to prevent the summary sheet from messing up my original references?
View 3 Replies
View Related
Dec 17, 2006
how do you store formulas so that they stay in the cells when all info is deleted? so i can use the same spreadsheet every week but all i have to do is select all and delete. if i do it now i lose al my formuals.
View 3 Replies
View Related
Aug 24, 2013
I work for a bank and we use an excel spreadsheet that has an in depth payment calculator used for creating monthly payment arrangements or calculating settlements on bank loans. I would like to add the following feature and I'm not quite sure exactly how to do it.
For example, imagine cell A1 will contain the full payoff of a loan. I want to be able to put a settlement offer in B1 and have C1 populate the percentage of the payoff that is. At the same time, I want to be able to delete the settlement offer in B1 and put a percentage in C1 and have it calculate that settlement offer in B1. The calculations/formulas to do these problems is simple, but what I need is to not lose the formulas in B1 and C1 if I were to go back and forth entering settlement offers vs settlement percentages.
View 2 Replies
View Related
Sep 6, 2007
With a macro i want to delete some lines in sheet1. In sheet2 however i have formulas that point on sheet1 .. like this
=sheet1!A1
=sheet1!A2
=sheet1!A3
Now when i delete line 2, the result would look like this
=sheet1!A1
#NV!
=sheet1!A2
Is there a way to make these formulas pointing at the same cells after line two is deleted? And i do not mean a work around, with different formulas. I just want the Formulas in sheet2 to remain unchanged, regardless of what happens in sheet1...
View 9 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
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
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
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
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
Dec 11, 2009
Say I have a method that iterates through a bunch of Sheets. I check the name of every sheet, if it starts with "Data", I need to make everything between A4 and AZ500 empty (either by clearing cells or deleting rows doesn't matter how, as long as the result is an empty sheet below A4).
View 2 Replies
View Related
Sep 21, 2009
I have an excel spreadsheet that contains about 1000 rows and about 25 columns. The file contains employee information, name, id, cost center, department, title, FTE...etc. Column E contains the cost center which is a 7 digit number (i.e. 8001234). Within the 1000 rows of data there somewhere to 70 cost centers. I would like to delete all rows where a cost center does not match a list of 13 cost centers. I'm thinking I need some kind of array where I type in the 13 cost centers in the code, but I'm unsure of the syntax within VBA.
Also, the second step of the code I'm looking for is to delete all columns except for Column B, E, J and L. Those, by the way are Name, Cost Center, Job Title and FTE.
View 2 Replies
View Related
Oct 26, 2011
I want to delete all rows in the column of the ActiveCell when the ActiveCell.value < 0.01. Could you tell me why the code below doesn't work? It deletes the right rows but keeps looping without stopping
Sub DeleteZeros()
Dim Col As Long
Dim StopRow As Long
Col = ActiveCell.Column
StopRow = Cells(Rows.Count, Col).End(xlUp).Row
Range("A1").Select
[Code] .........
View 6 Replies
View Related
Apr 25, 2007
I have a macro that deletes rows. For some reason, the macro bombs out when the selection seems to be too big. Why is that?
deleterow_min = Sheets("Sheet1").Cells(1, 6)
deleterow_max = Sheets("Sheet1").Cells(1, 7)
Rows(deleterow_min & ":" & deleterow_max).Select
Selection.Delete Shift:=xlUp
View 9 Replies
View Related
Aug 5, 2009
In a part of my code I have something that will delete all hidden rows, like:
For Z = 1 To TotalRows - 1
If Cells(TotalRows - Z, 1).EntireRow.Hidden Then
Cells(TotalRows - Z, 1).EntireRow.Delete
End If
Next Z
I've done it in reverse to be quicker, but it's still quite slow. Granted, I am deleting several thousand rows, but surely there's a quicker way? Thinking about it logically I would presume that (in general) selecting stuff first in Excel and then deleting them appears to be quicker, but I'm not sure how to pull that off.
View 9 Replies
View Related
Feb 21, 2009
Here's my problem. I have the following table:
View 5 Replies
View Related
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
Jun 26, 2008
I am having trouble with the .SpecialCells(xlCellTypeBlanks)
What I need to do is for a macro to first go through a range ("G8:G50"), Add a 0 value to any blank cell.
Then I want the macro to delete the entire row for any of the cells that have a value of 0 in that range of "G8:G50"
View 9 Replies
View Related
Nov 9, 2008
I have created a worksheet (through an import into MS Excel 2007) which contains 287,281 rows. However, the data I need is located in rows: 4, 67, 130, ... (or n+63) rows.
View 10 Replies
View Related
Mar 19, 2009
I want to delete every second row, because I have data with 0.25m interval and I want 0.5m interval. Why I try the following it doesn't work?
View 2 Replies
View Related
Apr 20, 2009
Loop for Deleting Rows
Whats wrong with my code ?
View 3 Replies
View Related
Apr 24, 2009
I would like to delete rows that are based on these conditions: First ,Do a loop from row 2 to last available row. - Delete rows with same column(column B) that has the same value. However I want the last available row with the same ID to remain.
- Delete Rows with any values in found in other sheet column B. When the value taken from the first sheet(ABC) is compared to the column B in second sheet(DEF), if they are equal, the row will be deleted in the second sheet and the rows in first sheet will main. I had attached an simple example with the 2 sheets. The Result are shown in the example.
View 2 Replies
View Related
Aug 24, 2009
I need some VBA to delete rows that are under the value of 10 in column H and keep the rest, is there a way of doing this
View 7 Replies
View Related
Oct 14, 2009
In my Excel 2003 worksheet I need a macro to search column B for duplicate entries. The data in column B is both numeric and string. If there are any duplicate entries, then I need the duplicate rows to be deleted.
View 4 Replies
View Related
Jan 18, 2012
I have data in the format below. I'm looking for a piece of code that will look down column B and if there are 30 rows in which all the values are zero then delete all rows from row 1 to the last row of those 30 rows.
Input_data AB106/01/2012 13:03:170206/01/2012 13:03:1816306/01/2012 13:03:1916406/01/2012 13:03:2016506/01/2012 13:03:2117606/01/2012 13:03:2224706/01/2012 13:03:2316806/01/2012 13:03:240906/01/2012 13:03:259.51006/01/2012 13:03:26191106/01/2012 13:03:27211206/01/2012 13:03:28161306/01/2012 13:03:2981406/01/2012 13:03:3001506/01/2012 13:03:31571606/01/2012 13:03:32801706/01/2012 13:03:331091806/01/2012 13:03:341331906/01/2012 13:03:35156
View 1 Replies
View Related
Feb 8, 2012
way to delete multiple rows in a large spreadsheet with automation Example:
84076 Cantor 10 Retail Tue Nov 08, 2011 160 REVISED Division 8 1600 Compulsion Done 84077 Cantor 10 Retail Thu Nov 17, 2011 160 CANCELLED Division 8 1600 Compulsion Done 84078 Cantor 10 Retail Thu Oct 13, 2011 88 Division 8 880 Compulsion Done
I would need to delete all 4 rows within the "Cancelled" Box, but in a spreadsheet of 12000 boxes of 4. so, probably a macro to find them all, and delete them would be the way to go.
View 2 Replies
View Related
Jul 25, 2012
I'm looking for a macro that will delete a row with a certain value (0.00) and the row below it.
View 9 Replies
View Related
