Delete rows based on header name

Question

I'm trying to delete a lot of rows in my Excel sheet.

My VBA is really simple:

Sub delNA()
    lr = Cells(Rows.Count, "A").End(xlUp).Row 'find last row
    For i = lr To 2 Step -1 'loop thru backwards, finish at 2 for headers
        If Cells(i, "H").Text = "#N/A" Then Rows(i).EntireRow.Delete
    Next i
End Sub

But my problem is that sometimes my headers are different. In this case the header BTEX (Sum) Is in H2, but sometimes that parameter is in G2 and sometimes it's in E2, so what I'm trying to do is make the VBA search for the header name, so instead of H the criteria is "BTEX (Sum)".

Is there a way to make the VBA run in the column where the value in row 2 is "BTEX (Sum)"

Answer 1

@Mikkel Astrup you first just use an for loop to find the col index you are looking for, in this case col index of the cell in row 2 have value "BTEX (Sum)"

Dim lColumn,indexCol As Long
dim ws as worksheet
dim headerKey as string
set ws = thisworkbook.worksheets("Sheet1")
lColumn = ws.Cells(2, Columns.Count).End(xlToLeft).Column
headerKey =  "BTEX (Sum)"
for indexCol = 1 to lColumn step 1 
if ws.cells(2,indexCol).value = headerKey then
' your code '  indexCol is the index of col you are looking for'
exit for ' exit the loop now
end if
Next

Answer 2

Give this a try:

Option Explicit 

Sub delNA()
  Const HEADER_TEXT As String = "BTEX (Sum)"
  Dim thisSheet As Worksheet
  Set thisSheet = ActiveSheet
  With thisSheet
    Dim myHeader As Range
    Set myHeader = .Cells.Find(What:=HEADER_TEXT, after:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not myHeader Is Nothing Then  'i.e. we found the header text somewhere
      Dim headerColumn As Long
      headerColumn = myHeader.Column

      Dim lastRow As Long
'Update here:
      lastRow = .Cells(.Rows.Count, headerColumn).End(xlUp).Row 'find last row
      Dim i As Long
      For i = lastRow To 2 Step -1 'loop thru backwards, finish at 2 for headers
'Update here:
          If IsError(.Cells(i, headerColumn).Value2) Then
            .Rows(i).EntireRow.Delete
          End If
      Next i
    Else
      MsgBox ("Could not find a column for the header text " & HEADER_TEXT)
    End If
  End With
End Sub

• It uses .Find() on the range to very quickly identify where your header column is and pops up an error message if it's not found (just in case)
  • NOTE: .Find() will use the current setting of anything in the Find dialog box that isn't explicitly set, and the next time you use the Find dialog box, the setting will be whatever they are set to by your code. i.e. the Find dialog box and the .Find() function share a common, persistent set of parameters.
• It assigns a worksheet variable to the current worksheet, just to ensure that, if this should run a while, a bored user doesn't click on another worksheet and break stuff.
• All worksheet rows explicitly reference thisSheet by using .Cells() and .Rows() (notice the leading .) because of the line: With thisSheet
• It uses .Value2 instead of .text (See here for the reason.)
• I declared Option Explicit at the beginning to ensure that all variables are declared before being used.
  • This is a good habit to be in as it will eliminate frustrating bugs from using MyText as a variable in one place and MyTxt as a variable somewhere else and not understanding why it isn't working.
• You now have the framework to make a more generic function by converting the Const declaration to a parameter that is accepted by delNA() and you can use it for any header row instead of this fixed one.