I am familiar with the macro DeleteRowOnCell, but I want to delete rows when a cell in a selected column has a formulae which is returning empty text. I tried the macro DeleteRowsFastest without success.
View 2 RepliesI have a row range 5:20004. In that row range column R may have 'QLD' in the cell.
I am trying to delete all rows within that range that have QLD in column R or delete the row if R <> 'NSW' (would be a better way just in case there is something other than QLD)
to do this on opening the file automatically.
Basically, I have a table of data and I want to make a VBA coding so that Any row with "cell value = 0 in column C" will be deleted.
But I can't get my code working and i don't know why. There's no error pop up either.
HTML Code:
Sub DeleteEmptyColumns2()
Dim Cell As Range
For Each Cell In Range("4:4")
If Cell = "0" Then
Cell.EntireColumn.Delete
End If
Next Cell
End Sub
I am attempting to remove columns in my table of data, using the above code seems to remove some them but not all, is this the best way to loop through a range?
I have some data in sheet 1 column A. However, the amount of data varies everytime. In addition, blanks cells may be present in column A as well.
I have some data in sheet 2 column A. The amount of data also varies everytime. In addition, blank cells maybe present as well.
what I need here is to match the values in column A sheet 1 to column A sheet 2, and if it matches to delete the entire row in sheet 1.
However, i want to keep all blank cells in column A sheet 1.
Sheet 2 blank cells to be removed if any.
how do I do that in VBA? A lot of forums talk about a specific value but none on non specific values.
I have tried a range of "sumproduct" and "count if" and I am still not having any luck.
What I would like excel to do is to count what surgery/hospital (B)is referring for what product (F)
help.xlsx
I'm trying to input a check into my macro. Basically the logic I'm trying to follow is:
If Row 1 = Row 2 then delete row 2,
ElseIf Row 1 Row 2 then Display MsgBox "Row 1 and Row 2 are different. Do you wish to continue?"
If user selects yes, resume
If user selects no, end sub and display MsgBox "Macro ended. Please correct changes manually"
I'm Trying to write a code to delete Value in Column E & F If value in Column A = 0. (same row)
I've tried a few methods but none of them work.
Any VBA code to delete rows in a subtotalled list?
The subtotal line and the associated rows should be deleted when subtotal equals zero.
I've seen a few examples of macros to delete the row if the first letter is something, but not if the first 2 is equal to something.
I'm looking to go thru every row that contains data and look in Column B to see if it starts with P4 or P5. If that's true, then delete the entire row.
I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.
For example:
1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5
If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.
I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
View 7 Replies View RelatedI have a sheet where there is a column showing all the totals of previous entries, however a couple of the cells shouldn't total but just show the last entry.
The cell is for an ongoing count of year to date applications received.
Can anyone tell me a formula that would look for the last data entered in a row and return that value
I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.
View 3 Replies View RelatedIm using this to loop through ID's for my code.
Sub Test2()
' Select cell A2, *first line of data*.
Range("A2").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub
How can I set a cell value = to what cell I the loop is currently on?
I have a formula =IF(D87=0,"",E87(D87-E87)/ABS(E87)) that returns a blank if D87 is Zero. if D87 and/or E87 is also Zero how would the formula look?
View 2 Replies View RelatedI have sheets that return their name with the following formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
All good, but on my summary sheet (that is summarising the data in each sheet) I want a cell to return me the name that each sheet is called, but simply saying ='SheetName'!B1 is not working
So how to I get the cell on my summary page to return me the text of the cell that is using the above formula.
I have created a file in which other users use on a daily basis to input their time in and time out hours per activity. There are 16 rows allowing them to input up to 16 activities completed in a day. More often than not they do not use all 16 rows. My issue is that these users are very computer illiterate and will often delete the zero that is in their time in or time out hours on unused rows. This results in their total time displaying "####" I have explained to each of them multiple times to input a zero and their problems will be corrected, but they can't seem to grasp that concept.
How to allow the users to input their times, but if they hit delete on a cell and the cell is blank for it to automatically populate the cell with a zero no matter what the user does. I have all other cells with formulas on the sheet locked, but if I lock these cells then they can't input their times.
I have a round formula,
=round(D65-E65,4)
The issue with this formula is if D65 is 0...then the result is a negative number. Just for clarity,
D65=0, E65=10%.....
So I end up with -10%. I can't have a negative number so I'm looking if the result is negative then the cell just equals 0. Is this possible with an IF formula?
Is it possible to run a macro from an excel cell formula?
ie.
=IF(A1=1, RUN MACRO, 0)
I have a 700 line spreadsheet and would like to delete the cell next to another cell if it contains a certain string. I.e if A1 to A3 equals "Test" then i would like to delete the contents of B1 to B3, But if A2 equals "Completed" then only B1 and B3 should be cleared.
View 4 Replies View RelatedSo I am having trouble figuring out how to do multiple text to equal multiple numbers. For instance right now I use =IF(E9="JOHN DOE","07", but I would like to have it where IF E9 = John Doe the cell shows 07, but IF the E9 = Sam Doe I want the cell to show 08, and the same IF E9 = Pat Doe the cell shows 09 and so on.
Meaning is there a way to have multiple functions go to a cell dependent on what is entered into E9 for example. I just want to have a number come out in one cell if a certain name is entered in a different cell. I loooked at this post [URL]....which got me started,
I am trying to insert a row every time a certain value appears in row.
Example attached.Example 1.xlsm
I need to copy the entire row that has the value 2 in column B and insert it in the line above (not fussy). It must be inserted and move all data in other rows accordingly.
I want to format cell A5 so as to where if I put in one of several keywords into it, cell A7 puts several letters (with spaces)
example A6:Normal = A7:AA AA AA Ive got about 10 select words so just one example would be awsome! 07 excel BTW
I am looking for a formula to allow an image file to show when a cell equals a certain text string. If it can be done can it be achieved with multiple images? I am making a database that will calculate from many variables my required glass sizes, given the size and window type and I was hoping to go one step further by making it raise a purchase order from a series of images and other related data.
View 14 Replies View RelatedIs there any way of playing a sound (or even better, speech) whenever a specific cell equals a certain value or contains a certain string (such as "Alarm")? This has to happen automatically as the cell updates and require no interaction.
View 3 Replies View RelatedI have a spreadsheet with a lot of account numbers. I want my Macro to highlight the row A:C if certain accounts are found in the spreadsheet.
For Example:
Account numbers "10998-0000" and "18999-0000" are allowed to be in the spreadsheet so don't want them highlighted. But Account "10110-0000" should be highlighted.
I know the Macro will be big but I was hopping something like:
If ActiveCell is not accounts .... I would then list all my account numbers... Then
and at the end a loop until ActiveCell is Empty.
I have this so far.
Range("A12").Select
MyColumn = 1
i = 12
Range("A" & i).Select
Do While ActiveCell.Value ""
[Code] ........
Is there a formula I can use to return a worksheet name. I want cell A1 on Sheet1 to = the name of Sheet2
View 4 Replies View RelatedI would like to return a unique list of values in column b sheet2 IF the value in Data!A is equal to A1 on sheet2.
I tried doing if formulas and v lookup formals but they dont give a unique list or only include in instance of the associated value.
I have students Kg-5th Gr. I need to identify the ones over-aged when I enter the information. The following is what I'm trying to use but I get Y1 for ever cell. I need it to Identify the student as Y1 for One year overage and Y2 for tow years overage.
View 3 Replies View Related