Deleting Cells With Certain Values

Jun 29, 2007

I know this is an easy question....what syntax would you use if you want a macro to search Column A for every cell with specific text, and then clear that cell? I can't seem to get the syntax right to get it to do what I want.

View 2 Replies


ADVERTISEMENT

Deleting Rows With Certain Values In VBA

Apr 8, 2009

I'm running into an issue when I try to generate a report for the largest five values in a report. Some of the cells have a zero divisor in their formula so they come up with the #DIV/0 error. I've put in an If statement to make those equal 0, but the LARGE formula still returns #NAME?. Any ideas on how to get the LARGE formula to work.

I would like to be able to delete the rows with zero in the reference column, so their data is not even in the report. Does anyone know how to do this in VBA?

View 9 Replies View Related

Deleting Duplicate Values In A Row

Aug 22, 2006

My excel looks like this. The rows have duplicate names which I want to delete:
TimeNamesNamesNamesNamesNamesNames
5 hoursBobTimTonyBobTom
7 HoursLisaPamPamHeatherKimKim
6 HoursLisaTimBobTimTim

I want the excel to look like this:
TimeNamesNamesNamesNames
5 hoursBobTimTonyTom
7 HoursLisaPamHeatherKim
6 HoursLisaTimBob

View 6 Replies View Related

Deleting Cell Values But Not Formulas?

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

Deleting Rows With Non-percentage Values

Jun 9, 2009

What I would like to do is to delete the rows in the attached Excel file highlighted in yellow (whole numbers) and to keep the rows with percentage values. Ideally I would like to have only the rows with the string "Group:" remaining along with the rows that have percentage values in them.

What I'm going to be doing is then transferring these groupings of data to another excel sheet in order to create a time series of performance by "team leads". If anybody would be willing to help me out with the first part of this I would really appreciate it. I have some idea of how to go about it using conditional logic, but I'm just not comfortable enough in VBA to do this efficiently. It would probably take me hours just to get a simple version working....

View 6 Replies View Related

Copy Values And Deleting Rows VBA?

Nov 13, 2012

I've tried to write a VBA code that copies a value down and deletes empty rows. The macro needs to copy down the value in column A from the above row if the current column is empty but delete a row if column B is empty. The following code will copy down the value but will trigger a 400 error and won't delete any rows. The desired effect is to have column A completely filled and the empty row (Row 5) and the row containing "Page 1" (Row 6) deleted.

Code:
Public Sub fillType()
Dim LastRow As Integer
Dim Count As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For Count = 0 To LastRow - 1
'If A column is empty, copy value from column above

[code].....

View 2 Replies View Related

VBA - Deleting Rows With Blank Values

Nov 13, 2006

I am trying to write a macro which will delete all rows where cells in column B are blank. I am new to macros and since I have only been able to record them, I am having a tough time hand-writing this one.

View 9 Replies View Related

Macro Deleting Values On Wrong Sheet

Jun 6, 2009

The below code is supposed to delete data on the last sheet of the workbook HeadsetOutTimeMay.xls however for some reason it is deleting the data on HeadsetOutTime.xls What am I doing wrong?

View 8 Replies View Related

Deleting Duplicate Rows And Summing Values?

Aug 6, 2012

I have a sheet whereby in column A i have company names and column B how much is invested in this company so:

A B
AAPL 10
GOOG 5
MSFT 15
APPL 5
IBM 20
GOOG 10

What i would like to do via macro is take all the duplicates sum them so that i see AAPL as 15 but only be left with one row. So we would be left with only 4 rows each with their summed weight.

View 9 Replies View Related

Deleting Rows Where Values Are Opposite & Equal

Mar 31, 2008

I have a spreadsheeet with values in columns F:K from row 10 onwards

I need VBA code that will delete the rows where there are values opposite and equal in columns F:K from row 10 owards for eg if F10 is 10000 and K10 is -10000, F 25 is 150000 and G25 is -10000, G29 is -175000 and H29 is 175000, then rowa 10,25 & 29 must be deleted

View 9 Replies View Related

Deleting Rows With Duplicate Values In A Column

Jun 26, 2009

In Column "C" there is a list of numbers ( these number realte to incidentsm and are called incident IDs) and I want to delete the rows that have duplicate incident IDs.

the row of numbers looks like this ....

View 9 Replies View Related

Filtering & Deleting Non Numerical Values Throughout Worksheet

May 11, 2006

how to find a certain criteria and delete the entire row that the criteria is in. I am more interested in finding certain criteria with vba, in my case any text/non numerical values and clear contents from that cell for the entire worksheet while retaining the rest of the cells that have numerical values in them. My data has --- in cells that represents missing data which would be easier if it was completely blank.

View 2 Replies View Related

Deleting Rows Based On Values In Certain Column

Oct 24, 2006

How can I delete rows in a worksheet using values in a certain column as criteria?
Example: I want to delete all rows that show a value of zero in column k. How is this done?

View 6 Replies View Related

Deleting Duplicate Rows Based On Cell Values

Nov 28, 2013

I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag

Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.

And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.

View 1 Replies View Related

Filling Rows According To Cell Values And Deleting Rows With No Values

Jan 25, 2013

I have inherited a spreadsheet with over 800 rows (and daily expanding) and 14 columns of data. I've attached a sheet that looks similar, with only 200 rows for reference. Here are my questions:

1. How can I delete all rows that contain no values?
2. I want to fill an entire row of data yellow if the values in the final two cells (L&M) in the row are equal, and red if they aren't. How can I do that?
3. Column A contains only dates, from oldest to newest. I'd like an obvious visual clue for when the months change. Currently it's a long, merged, blue-filled cell that says "March 2011", for instance. It can't be color because all cells need to be filled based on certain criteria (see #2), and borders aren't obvious enough.
4. I want to click on the row number on the very right, but have it select only columns A-M, is that possible?

I'm also having trouble with autofill. It seems to only work on parts of the spreadsheet. I don't know what settings may have been changed, but I do have autocomplete turned on, and no clue what to do despite hours of googling.

Test Tracking Sheet.xlsx‎

View 4 Replies View Related

Not Deleting Any Cells

Aug 15, 2007

The loop cycles through my sheet correctly but wont delete any cells.

View 10 Replies View Related

Deleting Visible Cells?

Apr 30, 2014

I have a data in which I have inserted a table with headers.

I then filter the type of data I want removed from the table using the drop down

Then goto special, visible cells only, press delete.

Now, the data is gone, but when I select all again to pull up the entire table, the data is gone, but rows are still there. So now I have tons of empty rows in between the rows of needed data. Any way of removing the rows in between as well as the data?

View 4 Replies View Related

Number Cells Deleting 0s

Jul 7, 2014

I need to store some numbers, all of which are prefixed by four or five zeros. When I format the cells as number, the zeros are erased. So, 0000012345 becomes 12345. The zeros are preserved when I store them as text, but I cannot do the functions that I need to do with the cells formatted as text.

How I can preserve the zeros in number format?

View 2 Replies View Related

Deleting The Name For A Group Of Cells

Feb 3, 2009

When you highlight a group of cells and give them a reference name- that name seems to be permanent. How do you delete one or add cells to the reference? Example attached book1.xls Cells A1-B3 are named "TheGroup".

View 2 Replies View Related

Deleting Specified Number Of Cells Using If

Mar 29, 2009

I have a column with "1" and "0". I'd like to have something like, if the value in a cell within the column is "0", then delete the 4 cells previous to the cell that contained "0" within the row.

Instead of deleting, the values can also be replaced with anything e.g., zero or n/a etc.

View 4 Replies View Related

Deleting And Inserting Cells

Apr 14, 2009

The delete function has disappeared from my EDIT menu in excel. Delete and Insert has also disappeared when I right Click on the spreadsheet.

View 11 Replies View Related

Deleting Cells With Userform

Dec 13, 2006

i have worksheet with peoples details in it... i have a userform with a combobox and a delete command button in it with the persons name in the combobox... and i cannot figure out how to delete the persons details with the combo box...

View 9 Replies View Related

Deleting Empty Cells ...?

Sep 22, 2007

Is there a way to delete empty cells, without having to do each one individually. I have 10,000 numbers with about 500 empty cells. I did a search, but found nothing...

View 9 Replies View Related

Deleting Rows With Cells Containing EL Or LE

Sep 1, 2008

I am trying to a method to delete rows that contain EL or LE in a specific cell in a set column as this will reduce my file down by nearly a third. Currently by doing it using a autofilter it kills my computer as there is so much data. Is there an easy way to reduce this?

Trade ID456465ELColumn B456456LE Column B8454321LEColumn B456456ELColumn B5464564LColumn B4564546LEColumn B

View 9 Replies View Related

Deleting Range Of Cells Using VBA

Jun 12, 2009

I need to have my macro select a certain range of cells with a specific product code and delete them. What would be the code for it (product code 107). Here is what i have so far:

Workbooks.Open Filename:=fNameAndPath, UpdateLinks:=1
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath2 & myFile3 & myExt, FileFormat:=xlNormal, CreateBackup:=False
Windows(myFile3).Activate
Cells.Select.Range ("A78:K153")
Selection.Delete
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath4 & myFile4 & myExt2, FileFormat:=xlCSV, CreateBackup:=False

View 9 Replies View Related

Exception - Deleting Only Certain Cells

Feb 27, 2007

I am trying to write a macro that looks at columns C-I in row 6, to check if the value is G, if it is then I want it to delete C5,6,7 & 8 and so on along to coumn I. So all that I am left with at the end of the macro are the values that weren't green without any blank gaps in the columns. I started using the following

If C6 = G Then
Range("C5:C8").Select
Selection.Delete Shift:=xlToLeft
End If

And using that same code for D6, E6 etc etc, but obviously, when it deletes the cells it shifts them left so the columns change values, so if C6 is equal to G then it deletes it but then what was D6 becomes C6.

View 2 Replies View Related

Deleting Entire Row That Has Cells With Certain Strings?

Mar 16, 2013

I am trying to delete entire row that has certain text, let say "hi". I was able to delete cells with exact word "hi", but was not able to delete cells that have texts other than "hi". ex) "hi myname." Below is my vba code, and I keep getting run-time error and cannot execute the code.

VB:
Sub HiDelete()
Dim srchRng As Range
Set srchRng = ActiveSheet.Range("g:g")
For i = srchRng.Rows.Count To 1 Step -1
If worksheefunction.Search("hi", Cells(i, 7)) > 0 Then Rows(i).Delete
Next i
End Sub

View 3 Replies View Related

Dictionary - Deleting Cells With Some Conditions

Jul 13, 2013

I have a VBA code which checks the value of two cells in "Sheet2", and when these values are in the fourth column of "Sheet4", then dictionary is saved and after that VBA delete Entire Rows in "Sheet4" which consists these values.

Please find the code below:

VB:
Sub dictionary1()
Application.ScreenUpdating = False

Dim dico1 As Scripting.Dictionary
Set dico1 = New Dictionary

[Code] .....

Now I wonder how to add more conditions like for example: "Delete all rows in "Sheet4" which have for instance in the 6th column text "Hello" AND which have in the 7th column value greater than 10". How can I change the existing code?

This is of course just an example, but the underlying question is how can I add to these dictionaries more conditions. Of course, we can still use "Sheet2" to add some value which we want dictionary to store.

I want to use for this only dictionaries because spreadsheet is large and filtering doesn't work at all...

View 7 Replies View Related

Use If / Then Statement - Deleting Range Of Cells

Jun 11, 2014

I have a sheet with thousands of rows and 45 columns and I need to delete a range of cells (G:Y) if the cell in column V = the cell in column X, and I need to do this by row. I think an if/then statement would work.

View 4 Replies View Related

Highlighting Or Deleting Specific Cells

May 16, 2009

I have Column A with 380 cells filled in with numbers (A1-A380) i.e. numbers like..456758, 567848 etc etc

Column B has a subset of those numbers, maybe 80-90 or so (B1-B90).

What I want to do is either highlight the numbers in Column A that also appear in Column B in yellow, OR delete them from Column A altogether

View 8 Replies View Related







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