Deleting rows in excel using VBA using column cell data criteria

[eastbiomed]

Thanks in advance! I have a excel 2010 spreadsheet that has a column "H". In column "H" many of the cells have the folowing data: "Department Open PM Count :" and in this same column "H" some of the cells directly above the cell data: "Department Open PM Count :" has a cell value of: "Call #" WhaT iw ould like to do is when this combination occurs, I would like to delete both rows. Here's a little sample:

COLUMN H
Call #
000123567
000485857
000344566
Department Open PM Count:

Call #
Department Open PM Count:

Call #
000123567
000485857
000344566
Department Open PM Count:



In my above example some areas of my column H have calls associated with them but in some there aren't any. When there are no call numbers listed, just the cell value Call# then immediately below is Department Open PM Count... I would like a VBA script that can recognize that combination and delete those two rows. This occurs frequently in column H.

Thanks again!

 

[Matth78]

I think you need to try something like this :

Public Sub Remov_Empty_Call()
    Dim LastRow As Long: LastRow = Range("H" & Rows.Count).End(xlUp).Row
    
    Dim l As Long
    For l = 1 To LastRow
        If Range("H" & l).Value = "Call #" And Range("H" & l + 1).Value = "Department Open PM Count:" Then
            Range(l & ":" & l + 1).Delete shift:=xlShiftUp
            l = l - 1
            LastRow = LastRow - 2
        End If
    Next l
End Sub

 

[eastbiomed]

Thanks in advance! I have a excel 2010 spreadsheet that has a column "H". In column "H" many of the cells have the folowing data: "Department Open PM Count :" and in this same column "H" some of the cells directly above the cell data: "Department Open PM Count :" has a cell value of: "Call #" WhaT iw ould like to do is when this combination occurs, I would like to delete both rows. Here's a little sample:

COLUMN H
Call #
000123567
000485857
000344566
Department Open PM Count:

Call #
Department Open PM Count:

Call #
000123567
000485857
000344566
Department Open PM Count:



In my above example some areas of my column H have calls associated with them but in some there aren't any. When there are no call numbers listed, just the cell value Call# then immediately below is Department Open PM Count... I would like a VBA script that can recognize that combination and delete those two rows. This occurs frequently in column H.

Thanks again!

Your VBA script worked flawlessly! To make my report perfect I need one more script...

In column B after runnin gyour script I have this:

COLUMN B

Department: ICU
BEC#
6748994
48475686
958568609
Department: OR
BEC#
24536241
657848393
Department: RESP
Department: EKG
Bec#
756474843

In my above example, Department: Resp is not associated with any BEC equipment ID numbers. At times, in my report there are three departments in a row not associated with any BEC labels. Here's what I am looking for. In Column B I would like to delete any row where column B starts with Dep, except for the ones that have BEC# in the row below them. Thank you so much, I am getting better at this but not at your level yet!

 

[eastbiomed]

Your VBA script worked flawlessly! To make my report perfect I need one more script...

In column B after running your script I have this:

COLUMN B

Department: ICU
BEC#
6748994
48475686
958568609
Department: OR
BEC#
24536241
657848393
Department: RESP
Department: EKG
Bec#
756474843

In my above example, Department: Resp is not associated with any BEC equipment ID numbers. At times, in my report there are three departments in a row not associated with any BEC labels. Here's what I am looking for. In Column B I would like to delete any row where column B starts with Dep, except for the ones that have BEC# in the row below them. Thank you so much, I am getting better at this but not at your level yet!

*********************************************************
Solved my own question!!! I changed your script a bit and changed the equal sign to the term "like" and changed all the column H references to column B and the searched term changed to DEP* Yay for us!