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.
I can't get this code to work which I want to delete the entire row if the cell in the specified range contains only 3 characters;
Sub CharCount() Dim cell As Range Dim bottomK As Integer bottomK = Range("D" & Rows.Count).End(xlUp).Row Dim rng As Range Set rng = Range("D2:D" & bottomK) For Each cell In rng If Len(cell) = 3 Then EntireRow(bottomK).Delete End If Next cell End Sub
I need to write a macro that will search column A and find the word hospital or HOSPITAL and if found delete the entire row. The code I have below will do this ONLY if hospital or HOSPITAL is the only word in the cell. However in my column A there are numerous words in each cell i.e Saint Mary's Hospital.
Sub KillRows() Dim rngNew As Range Dim rngDelete As Range Dim aCell As Range Dim lastrow As Long Set rngDelete = Nothing Set rngNew = Worksheets("Sheet1").Range("A1", Range("A65536").End(xlUp)) rngNew.Select For Each aCell In Selection Select Case aCell.Value...........
I'm trying to write a procedure which will search the contents of Column B of my worksheet for the word "Total". Whenever the word "Total" is found in a cell within Column B, the entire row should be deleted.
I have got a vacancy tracker spreadsheet and I need it to move an entire row from the 'Open' to the 'Closed' sheet based on the status in column K, i.e. 'Closed +', 'Closed -', and 'Closed + Achieve'. Once this has been done I want it to delete the entire row in the 'Open' sheet. At the moment it just cuts the entire row and then it is left blank and when I delete it manually the macro stops working completely.
Also I can only get it to move 'Closed +' and 'Closed -' and seem not to be able to add a command to move 'Closed + Achieve' rows.
This is what I have got so far: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("K")) Is Nothing Then Exit Sub Application.EnableEvents = False If Target.Value = "Closed +" Then
1). For each cell that in the range that has a Red background delete the entire column I have attached a sample spreadsheet where I have tried to do this. It's not quite working. It does not seem to work when there are RED cells next to each other.
I know that I can do a simple thing like column("A:A"), but I'd rather have it in a loop as in the sample code, in case the columns change in the future.
2). The second task is to delete the rows in the spreadsheet where the first cell in the row is a blank.
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