Delete entire row that is blank in column by header name

[benntw]

I have a code that finds the header by name. I also have a code for deleting entire row(s) that have no value in a cell. What I am trying to do is find a column, by the header name, and delete all rows that have no value in this column.

Here is the code I am using to find the column

Dim rngAddress As Range

Set rngAddress = Range("A1:ZA1").Find("JOBNUMBER")
If rngAddress Is Nothing Then
MsgBox "Address column was not found."
Exit Sub
End If
Range(rngAddress, rngAddress.End(xlDown)).Select

Here is the code I found to delete the entire row

Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

I tried to figure out how to combine without any luck. Hope someone can help.

 

[Trebor76]

Hi benntw,

Try this:

Option Explicit
Sub Macro1()

    Dim rngAddress As Range
    Dim lngLastRow As Long
    Dim strFoundCol As String

    Set rngAddress = Range("A1:ZA1").Find("JOBNUMBER")
    If rngAddress Is Nothing Then
        MsgBox "Address column was not found."
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    strFoundCol = Split(rngAddress.Address, "$")(1)
    lngLastRow = Cells(Rows.Count, strFoundCol).End(xlUp).Row
    Range(strFoundCol & "2:" & strFoundCol & lngLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp
    Application.ScreenUpdating = True
     
End Sub

Regards,

Robert

 

[Fluff]

Another option

Sub benntw()

    Dim rngAddress As Range

    Set rngAddress = Range("A1:ZA1").Find("JOBNUMBER")
    If rngAddress Is Nothing Then
        MsgBox "Address column was not found."
        Exit Sub
    End If
    
    rngAddress.EntireColumn.SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[benntw]

Hi benntw,

Try this:

Option Explicit
Sub Macro1()

    Dim rngAddress As Range
    Dim lngLastRow As Long
    Dim strFoundCol As String

    Set rngAddress = Range("A1:ZA1").Find("JOBNUMBER")
    If rngAddress Is Nothing Then
        MsgBox "Address column was not found."
        Exit Sub
    End If
  
    Application.ScreenUpdating = False
    strFoundCol = Split(rngAddress.Address, "$")(1)
    lngLastRow = Cells(Rows.Count, strFoundCol).End(xlUp).Row
    Range(strFoundCol & "2:" & strFoundCol & lngLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp
    Application.ScreenUpdating = True
   
End Sub

Regards,

Robert

Hi benntw,

Try this:

Option Explicit
Sub Macro1()

    Dim rngAddress As Range
    Dim lngLastRow As Long
    Dim strFoundCol As String

    Set rngAddress = Range("A1:ZA1").Find("JOBNUMBER")
    If rngAddress Is Nothing Then
        MsgBox "Address column was not found."
        Exit Sub
    End If
   
    Application.ScreenUpdating = False
    strFoundCol = Split(rngAddress.Address, "$")(1)
    lngLastRow = Cells(Rows.Count, strFoundCol).End(xlUp).Row
    Range(strFoundCol & "2:" & strFoundCol & lngLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

Thank you

 

[benntw]

Another option

Sub benntw()

    Dim rngAddress As Range

    Set rngAddress = Range("A1:ZA1").Find("JOBNUMBER")
    If rngAddress Is Nothing Then
        MsgBox "Address column was not found."
        Exit Sub
    End If
   
    rngAddress.EntireColumn.SpecialCells(xlBlanks).EntireRow.Delete
End Sub

Thank you very much. Was realy quick and worked perfect. I was close to that when I was trying. Love learning new things. Appreciate it.

 

[Fluff]

Glad we could help & thanks for the feedback.