Delete entire row that is blank in column by header name

benntw

Board Regular
Joined
Feb 17, 2014
Messages
222
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi benntw,

Try this:

VBA Code:
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
 
Upvote 0
Another option
VBA Code:
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
 
Upvote 0
Hi benntw,

Try this:

VBA Code:
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:

VBA Code:
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
 
Upvote 0
Another option
VBA Code:
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.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top