Moving to first Instance of a Value
 

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?

Moving to first Instance of a Value
 

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?

Moving to first Instance of a Value
 

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?

Moving to first Instance of a Value
 

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?

Moving to first Instance of a Value
 

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?

Moving to first Instance of a Value
 

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?

Moving to first Instance of a Value
 

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?

Moving to first Instance of a Value
 

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?

Moving to first Instance of a Value
 

Select Column B
do Edit=find
and put Branch in the textbox, then hit Find.

Does it select B6?

That is what the code is doing.

Turn on the Macro Recorder

to the above manually

Turn off the Macro Recorder.

Look at the code recorded.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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?

Moving to first Instance of a Value
 

it's a damned merged cell. if the merged cell is B2 to E6, what can i do?
this spread was spit out as a pdf from machine and converted to excel.

i just receive it in it's current state.


"Tom Ogilvy" wrote in message
...
Select Column B
do Edit=find
and put Branch in the textbox, then hit Find.

Does it select B6?

That is what the code is doing.

Turn on the Macro Recorder

to the above manually

Turn off the Macro Recorder.

Look at the code recorded.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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?

Moving to first Instance of a Value
 

is there a way to unmerge all merged cells on a sheet?

"Tom Ogilvy" wrote in message
...
Select Column B
do Edit=find
and put Branch in the textbox, then hit Find.

Does it select B6?

That is what the code is doing.

Turn on the Macro Recorder

to the above manually

Turn off the Macro Recorder.

Look at the code recorded.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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?

Moving to first Instance of a Value
 

select all cells by clicking on the blank square in the upper left corner of
the sheet (above and to the left of A1)

Format=Cells, alignment tab and unchecked Merged.

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
is there a way to unmerge all merged cells on a sheet?

"Tom Ogilvy" wrote in message
...
Select Column B
do Edit=find
and put Branch in the textbox, then hit Find.

Does it select B6?

That is what the code is doing.

Turn on the Macro Recorder

to the above manually

Turn off the Macro Recorder.

Look at the code recorded.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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?

Moving to first Instance of a Value
 

for VBA

cells.UnMerge

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
is there a way to unmerge all merged cells on a sheet?

"Tom Ogilvy" wrote in message
...
Select Column B
do Edit=find
and put Branch in the textbox, then hit Find.

Does it select B6?

That is what the code is doing.

Turn on the Macro Recorder

to the above manually

Turn off the Macro Recorder.

Look at the code recorded.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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?

Moving to first Instance of a Value
 

thanks for staying with me on this one. i think i'm ok now.


"Tom Ogilvy" wrote in message
...
for VBA

cells.UnMerge

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
is there a way to unmerge all merged cells on a sheet?

"Tom Ogilvy" wrote in message
...
Select Column B
do Edit=find
and put Branch in the textbox, then hit Find.

Does it select B6?

That is what the code is doing.

Turn on the Macro Recorder

to the above manually

Turn off the Macro Recorder.

Look at the code recorded.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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?

Moving to first Instance of a Value
 

one last question. can you look at your find and delete rows code and help
me find why 1 row always remains after deleting from B1 to where Branch
first occurs?

"Tom Ogilvy" wrote in message
...
for VBA

cells.UnMerge

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
is there a way to unmerge all merged cells on a sheet?

"Tom Ogilvy" wrote in message
...
Select Column B
do Edit=find
and put Branch in the textbox, then hit Find.

Does it select B6?

That is what the code is doing.

Turn on the Macro Recorder

to the above manually

Turn off the Macro Recorder.

Look at the code recorded.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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?

Moving to first Instance of a Value
 

Yes, I can tell you why. Because that is what you asked for.

delete all rows between B1 and 1 row before 1st cell containing Branch".

If you want to include row1, then

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("B1"), _
rng1.Offset(-1, 0)).EntireRow _
.Delete
Else
MsgBox "Branch not found"
End If

End Sub

change B2 to B1 as I have done above.

--
Regards,
Tom Ogilvy



"scott" wrote in message
...
one last question. can you look at your find and delete rows code and help
me find why 1 row always remains after deleting from B1 to where Branch
first occurs?

"Tom Ogilvy" wrote in message
...
for VBA

cells.UnMerge

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
is there a way to unmerge all merged cells on a sheet?

"Tom Ogilvy" wrote in message
...
Select Column B
do Edit=find
and put Branch in the textbox, then hit Find.

Does it select B6?

That is what the code is doing.

Turn on the Macro Recorder

to the above manually

Turn off the Macro Recorder.

Look at the code recorded.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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?

Moving to first Instance of a Value
 

i finally saw that. thanks very much.


"Tom Ogilvy" wrote in message
...
Yes, I can tell you why. Because that is what you asked for.

delete all rows between B1 and 1 row before 1st cell containing Branch".

If you want to include row1, then

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("B1"), _
rng1.Offset(-1, 0)).EntireRow _
.Delete
Else
MsgBox "Branch not found"
End If

End Sub

change B2 to B1 as I have done above.

--
Regards,
Tom Ogilvy



"scott" wrote in message
...
one last question. can you look at your find and delete rows code and
help
me find why 1 row always remains after deleting from B1 to where Branch
first occurs?

"Tom Ogilvy" wrote in message
...
for VBA

cells.UnMerge

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
is there a way to unmerge all merged cells on a sheet?

"Tom Ogilvy" wrote in message
...
Select Column B
do Edit=find
and put Branch in the textbox, then hit Find.

Does it select B6?

That is what the code is doing.

Turn on the Macro Recorder

to the above manually

Turn off the Macro Recorder.

Look at the code recorded.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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?