I have a sheet in Excel 2004 for Mac that is a patient list with over 2000 patients. I need a rule that will compare values (patient IDs and responsible party ID) in two adjacent cells, if the values are different, to delete the entire row. The goal is to get rid of patients (rows) that are not the responsible party (responsible party ID).
I need an easy code that searches all of column A and deletes the entire row if the cell has the value "-". It needs to find the last row of data using something like LastRow = Range("A" & Rows.Count).End(xlUp).Row
How to correct my formula because it does not work?
Here the formula: =COUNTIFS(Data!C2:C24005,A17,Data!M2:M11149,"<=0")
I need to insert the formula in column C (Findings tab) which counts the rows in Column M (tab named "Data") that equal A17 (Column A in "Findings" tab) and which are less or equal zero. In addition if I drag the formula down I want to only the values be entered in highlighted in blue cells in column C (SKUs With Zero Sales in "Findings" tab) and empty cells in not highlighted cells. I attached images of these two tabs: Data and Findings.
I am trying to search a column for specific values.... if the cells in the column equal X, Y, or Z then I want to keep them. Anything else, should be deleted....and not just the cell the entire row that it is in.
So I'd like it to search column E for a number of different values, if equal keep the data. If not (say E6 is not any of those values) then delete that row (all of row 6)
Located in column W are amounts. if this amount is les than or equal to zero, I want to look at column A to see what account number is associated with that amount. then delete any row with that account number and also any empty row that may be directly above it.
Starting in cell F3, if Column F is greater than or equal to zero, delete the entire row and continue deleting rows until Column A has contents in it. Then go to Column F in that same row that had contents in Column A, if the contents in that row of Column F are greater than or equal to zero, delete the entire row and continue deleting rows until Column A has contents in it. Persist with this pattern until every row in Column F has been checked.
I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.
I'm having trouble with a small vba macro. At the end of the macro I test to see if two variables are equal and then print out true or false. However, for some reason even though the variables are equal vba is not treating them that way. I have put the values that represent the variables on a spreadsheet and used the if(x1=x2) formula and it says it is true, also, when I debug the macro and watch the values when it comes to test the logical expression the numbers are the same. I don't understand why vba does not say that the two variables are equal. I have attached a screenshot of the breakpoint where I double check the values are equal.
I have two cells, both of which have drop down lists for options 'a' & 'b'. When one equals 'a' the other should be restricted into only saying 'b' and vice versa. I would also like the user to be reminded that he can only select 'a' once from the two menus. Ideally I would like to do this in a formula rather than VBA?
I have a spreadsheet with some cells which return values using INDEX and MATCH to bring back the entire details of a person based on there membership number. However I require the comments to be attached with the returned values. The comments have there image in, rather than just text.
I have found some answers with vba, which do the process, however you had to manually enter the cell, from which the comment was to be copied from, into a pop up box. This involved finding the box myself to then select the box to take the comment from. In doing this I had found and read the comment, which is the is the process I'm trying to bypass.
I've been pounding away at a project for two days trying to determine a way to identify a solution, to no avail.
Here is some experimental code. Under the assumption that in Column "A", every fifth line contains some text (a, b, c, etc) I want to place a function in column B that would identify the closest occupied cell above and to the left of the cell containing the function. The sub achieves this, but the function has a problem because of the Activecell line. Is there a way to identify the location of the cell containing the function to act as a reference point for the range?
Sub find_last_heading() Application.ScreenUpdating = False x = ActiveCell.Address ActiveCell.Offset(0, -1).Select last_heading = ActiveCell.Text Do Until last_heading "" ActiveCell.Offset(-1, 0).Select last_heading = ActiveCell.Text Loop MsgBox last_heading Range(x).Select End Sub Function cathead(Optional reference)
I need to identify rows where the contents of a given cell are equivalent to the cell below. I have managed to get this working based on iterating through the cells and selecting them both for cutting.
However it doesn't work effectively: It seems to be leaving blank lines in the original sheet and further fails after the first execution. I would be grateful for any suggestions on a good method to perform the following operations: (1)to compare the cell with the cell below I have used
If cell = cell.Offset(1, 0) Then
(2)to act on the rows for the two connected rows
' Loop through rows
Dim TheExtract As String Dim cell As Range
'Application. ScreenUpdating = False For Each cell In Worksheets("Sheet 1"). _ Range("A1", Worksheets("Sheet 1").Range("A65536").End(xlUp)) TheExtract = cell If cell = cell.Offset(1, 0) Then cell.Rows("1:2").EntireRow.Select Selection.Cut Sheets("Test").Select ActiveCell.Offset(3, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown End If Next cell 'Application.ScreenUpdating = True
I would like to program an Input Box that looks at the cell next to it to see if there is a value equal to zero in it. If there is a Zero in the cell an Input Box will popup and ask "what was the result". It would keep going down the column until there are no more values in column A.
In this example the Input Box would popup for the second line and the third line and then populate the values entered into C2 and C3 accordingly.
I currently, manually create my production work orders using excel (in-house ERP system not good enough). Rather than manually type in the names of products to be produced, I would like to have the number 1 = a production item and 2 = another and so on.....So basically when I type 1 in a cell it will automatically display a production item.
I imagine this to be a process where i would type all the names of my production items in individual cells, then create a formula to make 1 = a specific cell, 2, 3, 4, etc.....
code that will go to a cell in Sheet1 column A that has the same or closest value to cell B11 in another workbook. The number values in the column are to three decimal places and the run in order down the column from lowest value to hightest.