Finding criteria and removing matching rows (Range issue?)

[Ronny Hamida]

I hope I can explain this one correctly.

Every time they do a system change around here, the columns of our data
change places. Luckly the headers are named the same in every case.

Therefore, part of my existing macro finds those header names and defines
that particular cell as a name within the worksheet. For instance, the macro
will search for the column header, "Title Classification", and define an
actual name of "Title" so that it can be used in other parts of the macro.
The defined "Title" then refers to cell x1 (where x is the "Title
Classification" column.) "Title" does not, however, refer to the entire
column.

Here's the dilema:

I would like to search the "Title" column for information as such:

Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "E").Value, 9) = "Super Man" Then
Cells(row_index, "E").EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

In the case above, it is assumed that column "E" contains the data. Truly,
that's not the case. It could be column "B", or "C", or anything else. One
thing for sure - It's the same column as the defined "Title" header.

The question:

Can I change where it says "E" to reflect the same column as "Title"? This
way, no matter where the column is, it will already be found and defined from
previous code?

Thanks to all who can help!

Cheers,

Ronny

 

[Dave Peterson]

Dim TitleCol As long
Dim TitleCell as range

with activesheet
with .rows(1) 'is the title in row 1?
set titlecell = .cells.find(what:="Title Classification", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

if titlecell is nothing then
msgbox "Not found!!!
exit sub
end if
end with

titlecol = titlecell.column

Application.ScreenUpdating = False
lastrow = .Cells(Rows.Count, titlecol).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Left(.Cells(row_index, titlecol).Value, 9) = "Super Man" Then
.rows(row_index).EntireRow.Delete
End If
Next row_index
Application.ScreenUpdating = True
end with

========

I think I'd use:

If lcase(Left(.Cells(row_index, titlecol).Value, 9)) = lcase("Super Man") Then

(Just in case)

 

[Ronny Hamida]

Thank you, Dave!

Dim TitleCol As long
Dim TitleCell as range

with activesheet
with .rows(1) 'is the title in row 1?
set titlecell = .cells.find(what:="Title Classification", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

if titlecell is nothing then
msgbox "Not found!!!
exit sub
end if
end with

titlecol = titlecell.column

Application.ScreenUpdating = False
lastrow = .Cells(Rows.Count, titlecol).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Left(.Cells(row_index, titlecol).Value, 9) = "Super Man" Then
.rows(row_index).EntireRow.Delete
End If
Next row_index
Application.ScreenUpdating = True
end with

========

I think I'd use:

If lcase(Left(.Cells(row_index, titlecol).Value, 9)) = lcase("Super Man") Then

(Just in case)