Delete Blank Row Before Particular Text

[Mohanmoni]

Hi,

I am looking for VBA Code that can delete blank row above particular text. For Example..

A1: John Kenneth
A2:
A3: Manager
A4: California

Like this i have 10,000 records, Now I want delete the row A2 which is above "Manager" keyword. Please suggest.

Thank You,
Mohan S

 

[Momentman]

Here's one way to do it

Sub DeleteBlankText()
    Dim I As Long, Lastrow As Long
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For I = Lastrow To 2 Step -1
        If Cells(I, "A") = "" And Cells(I + 1, "A") = "Manager" Then
            Rows(I).EntireRow.Delete
        End If
    Next I
End Sub

Thats in its basic form

 

[Mohanmoni]

Hi Momentman,

Thanks for your reply...but this code works only particular word called "Manager". I want keyword consideration example.. this may consider "Executive Manager" or "Digital Manager"... But "Manager" is common keyword. Please suggest code for this.

 

[Peter_SSs]

This code appears longer but should work much quicker on your fairly large data set. Test in a copy of your workbook.

Sub Del_Rws()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, lc As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 2 To UBound(a) - 1
    If IsEmpty(a(i, 1)) Then
      If InStr(1, a(i + 1, 1), "manager", vbTextCompare) > 0 Then
        b(i, 1) = 1
        k = k + 1
      End If
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    lc = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
    With Range("A1").Resize(UBound(b), lc)
      .Columns(lc).Value = b
      .Sort Key1:=.Columns(lc), Order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Offset(1).Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = False
  End If
End Sub

 

[Momentman]

Hi Momentman,

Thanks for your reply...but this code works only particular word called "Manager". I want keyword consideration example.. this may consider "Executive Manager" or "Digital Manager"... But "Manager" is common keyword. Please suggest code for this.

Surely Peter's code will be faster. But to answer your question, this is what i would do

Sub DeleteBlankText()
    Dim I As Long, Lastrow As Long
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For I = Lastrow To 2 Step -1
        If Cells(I, "A") = "" And InStr(1, Cells(I + 1, "A"), "Manager") > 0 Then
            Rows(I).EntireRow.Delete
        End If
    Next I
End Sub