Moving to first Instance of a Value

[scott]

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?

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[scott]

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

 

[Tom Ogilvy]

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

 

[scott]

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

 

[Tom Ogilvy]

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

Lookat:=xlWhole, _


to

Lookat:=xlPart, _

 

[scott]

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]

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.

 

[scott]

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.

 

[scott]

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

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

for VBA

cells.UnMerge

--
Regards,
Tom Ogilvy

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

 

[scott]

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

 

[scott]

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]

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.

 

[scott]

i finally saw that. thanks very much.

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


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.