I just need to move down column B to find the 1st cell containing a word.
Then I'll be able to get it's row and column.

Any help?

This moves donw from the activecell cell.
do until activecell.value = ""
ActiveCell.Offset(1, 0).Range("A1").Select
if activecell.value = "Text" then
Z= activecell.row
end if
loop
"scott" wrote:

I just need to move down column B to find the 1st cell containing a word.
Then I'll be able to get it's row and column.

Any help?

Well, the column is Column B. For the row

Range("B1").Select
do while lcase(ActiveCell) < "word" and ActiveCell < ""
activeCell.Offset(1,0).Select
Loop
if ActiveCell = "Word" then
myrow = activeCell.row
mycolumn = activecell.column
end if

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I just need to move down column B to find the 1st cell containing a word.
Then I'll be able to get it's row and column.

Any help?

I need a little help. In cell B6 the value = "Branch". I'm just trying to
say, "Start at B1, find 1st cell that contains Branch and delete all rows
between B1 and 1 row before 1st cell containing Branch".

Code runs an infinate loop. I tried several variations of suggested code,
but can't figure where i'm wrong.

Function CleanHeader()

dim r as integer, sString as string
sString = "Branch"

Do until activecell.value = ""
' ActiveCell.Offset(1,0).Range("A1").Select
Range("B1").Select

If activecell.value = sString Then
r= activecell.row
End If
loop

Dim startCell As Range, endCell As Range

ActiveCell.Offset(1,0).Range("A1").Select

Set startCell = ActiveCell
Set endCell = startCell.Offset(r-1, 0)
Range(startCell, endCell).EntireRow.Delete
End Function



"scott" wrote in message
...
I just need to move down column B to find the 1st cell containing a word.
Then I'll be able to get it's row and column.

Any help?

Sub Tester3()
Dim rng As Range, rng1 As Range
Set rng = Cells(Rows.Count, 2).End(xlUp)
Set rng = Range(Range("B1"), rng)
Set rng1 = rng.Find(What:="Branch", _
After:=Range("B1"), _
LookIn:=xlValues, _
Lookat:=xlWhole, _
SearchOrder:=xlByRows)
If Not rng1 Is Nothing Then
Range(Range("B2"), _
rng1.Offset(-1, 0)).EntireRow _
.Delete
Else
MsgBox "Branch not found"
End If

End Sub
--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I need a little help. In cell B6 the value = "Branch". I'm just trying to
say, "Start at B1, find 1st cell that contains Branch and delete all rows
between B1 and 1 row before 1st cell containing Branch".

Code runs an infinate loop. I tried several variations of suggested code,
but can't figure where i'm wrong.

Function CleanHeader()

dim r as integer, sString as string
sString = "Branch"

Do until activecell.value = ""
' ActiveCell.Offset(1,0).Range("A1").Select
Range("B1").Select

If activecell.value = sString Then
r= activecell.row
End If
loop

Dim startCell As Range, endCell As Range

ActiveCell.Offset(1,0).Range("A1").Select

Set startCell = ActiveCell
Set endCell = startCell.Offset(r-1, 0)
Range(startCell, endCell).EntireRow.Delete
End Function



"scott" wrote in message
...
I just need to move down column B to find the 1st cell containing a word.
Then I'll be able to get it's row and column.

Any help?

for some reason, it doesn't find the word branch in B6. I noticed you set
rng twice. what do you think?


"Tom Ogilvy" wrote in message
...
Sub Tester3()
Dim rng As Range, rng1 As Range
Set rng = Cells(Rows.Count, 2).End(xlUp)
Set rng = Range(Range("B1"), rng)
Set rng1 = rng.Find(What:="Branch", _
After:=Range("B1"), _
LookIn:=xlValues, _
Lookat:=xlWhole, _
SearchOrder:=xlByRows)
If Not rng1 Is Nothing Then
Range(Range("B2"), _
rng1.Offset(-1, 0)).EntireRow _
.Delete
Else
MsgBox "Branch not found"
End If

End Sub
--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I need a little help. In cell B6 the value = "Branch". I'm just trying to
say, "Start at B1, find 1st cell that contains Branch and delete all rows
between B1 and 1 row before 1st cell containing Branch".

Code runs an infinate loop. I tried several variations of suggested code,
but can't figure where i'm wrong.

Function CleanHeader()

dim r as integer, sString as string
sString = "Branch"

Do until activecell.value = ""
' ActiveCell.Offset(1,0).Range("A1").Select
Range("B1").Select

If activecell.value = sString Then
r= activecell.row
End If
loop

Dim startCell As Range, endCell As Range

ActiveCell.Offset(1,0).Range("A1").Select

Set startCell = ActiveCell
Set endCell = startCell.Offset(r-1, 0)
Range(startCell, endCell).EntireRow.Delete
End Function



"scott" wrote in message
...
I just need to move down column B to find the 1st cell containing a
word.
Then I'll be able to get it's row and column.

Any help?

I think it worked fine for me. maybe you have more than just Branch in B6.
Try changing

Lookat:=xlWhole, _


to

Lookat:=xlPart, _

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
for some reason, it doesn't find the word branch in B6. I noticed you set
rng twice. what do you think?


"Tom Ogilvy" wrote in message
...
Sub Tester3()
Dim rng As Range, rng1 As Range
Set rng = Cells(Rows.Count, 2).End(xlUp)
Set rng = Range(Range("B1"), rng)
Set rng1 = rng.Find(What:="Branch", _
After:=Range("B1"), _
LookIn:=xlValues, _
Lookat:=xlWhole, _
SearchOrder:=xlByRows)
If Not rng1 Is Nothing Then
Range(Range("B2"), _
rng1.Offset(-1, 0)).EntireRow _
.Delete
Else
MsgBox "Branch not found"
End If

End Sub
--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I need a little help. In cell B6 the value = "Branch". I'm just trying
to
say, "Start at B1, find 1st cell that contains Branch and delete all
rows
between B1 and 1 row before 1st cell containing Branch".

Code runs an infinate loop. I tried several variations of suggested
code,
but can't figure where i'm wrong.

Function CleanHeader()

dim r as integer, sString as string
sString = "Branch"

Do until activecell.value = ""
' ActiveCell.Offset(1,0).Range("A1").Select
Range("B1").Select

If activecell.value = sString Then
r= activecell.row
End If
loop

Dim startCell As Range, endCell As Range

ActiveCell.Offset(1,0).Range("A1").Select

Set startCell = ActiveCell
Set endCell = startCell.Offset(r-1, 0)
Range(startCell, endCell).EntireRow.Delete
End Function



"scott" wrote in message
...
I just need to move down column B to find the 1st cell containing a
word.
Then I'll be able to get it's row and column.

Any help?

i tried part, whole to no avail. Branch is in B6 and fartjer down in column
B. I'm just trying to delete the rows between A1 and B6 where Branch appears
first.

Could it be because of proper case?


"Tom Ogilvy" wrote in message
...
I think it worked fine for me. maybe you have more than just Branch in B6.
Try changing

Lookat:=xlWhole, _


to

Lookat:=xlPart, _

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
for some reason, it doesn't find the word branch in B6. I noticed you set
rng twice. what do you think?


"Tom Ogilvy" wrote in message
...
Sub Tester3()
Dim rng As Range, rng1 As Range
Set rng = Cells(Rows.Count, 2).End(xlUp)
Set rng = Range(Range("B1"), rng)
Set rng1 = rng.Find(What:="Branch", _
After:=Range("B1"), _
LookIn:=xlValues, _
Lookat:=xlWhole, _
SearchOrder:=xlByRows)
If Not rng1 Is Nothing Then
Range(Range("B2"), _
rng1.Offset(-1, 0)).EntireRow _
.Delete
Else
MsgBox "Branch not found"
End If

End Sub
--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I need a little help. In cell B6 the value = "Branch". I'm just trying
to
say, "Start at B1, find 1st cell that contains Branch and delete all
rows
between B1 and 1 row before 1st cell containing Branch".

Code runs an infinate loop. I tried several variations of suggested
code,
but can't figure where i'm wrong.

Function CleanHeader()

dim r as integer, sString as string
sString = "Branch"

Do until activecell.value = ""
' ActiveCell.Offset(1,0).Range("A1").Select
Range("B1").Select

If activecell.value = sString Then
r= activecell.row
End If
loop

Dim startCell As Range, endCell As Range

ActiveCell.Offset(1,0).Range("A1").Select

Set startCell = ActiveCell
Set endCell = startCell.Offset(r-1, 0)
Range(startCell, endCell).EntireRow.Delete
End Function



"scott" wrote in message
...
I just need to move down column B to find the 1st cell containing a
word.
Then I'll be able to get it's row and column.

Any help?