Excel to select a row based on previous rows
 

Hi guys,

I started a post yesterday, and have made some headroom, but still nee
some assistance.

I have been working on some code such that if three consecutive cell
meet a certain criteria (i.e are blank), the entire row of the thir
blank cell is selected.

Thats not where my code will end, but think I will be able to mov
along from there, or at least I will hope.

Simply put, if cells k5,k6,and k7 are all blank, the entire 7th row i
selected and stops at that point. Ideally, i would love to have i
search if three consecutive rows are entirely blank and select th
third row, but I do not know if that is possible.

Ive had some code that either would select rows that met this criteri
and kept going, or selected rows that did not meet this criteria, but
can not seem to stick the code for this one.

I am relatively new to VBA, but feel that I have a strong enoug
background to work through code should I see it.

Any help will be greatly appreciated.

Broc

--
Message posted from http://www.ExcelForum.com

Excel to select a row based on previous rows
 

Range("K1").Select
For N = 0 To ActiveSheet.UsedRange.Rows.Count
If Len(ActiveCell.Offset(0, 0).Value) = 0 And _
Len(ActiveCell.Offset(1, 0).Value) = 0 And _
Len(ActiveCell.Offset(2, 0).Value) = 0 Then
Rows(ActiveCell.Offset(2, 0).Row & ":" & ActiveCell.Offset(2
0).Row).Select
Exit Sub
Else
ActiveCell.Offset(1, 0).Select
End If
Next

--
Message posted from http://www.ExcelForum.com

Excel to select a row based on previous rows
 

I greatly appreciate your help. With a few tweaks, I made afew change
and am well on my way.

I have to admit I am pretty embarrassed that I was not able to deciphe
such a code myself. My attempts at this were much more complicated.

Do you know a way that I can count how many cells are in a column unti
i reach a certain value (which is identified by a text phrase).

Thanks again,

Broc

--
Message posted from http://www.ExcelForum.com

Excel to select a row based on previous rows
 

The number of cells in a column until you reach a certain
text phrase is equal to the row number that the text
phrase is found in minus 1. The following code will
determine this row number. Just subtract 1 from it. The
code assumes that the column is column A and the text
phrase is "monkey". Change to suit. Change the
argument "xlWhole" to "xlPart" if the text phrase forms
only part of the cell value or their are potentially blank
spaces included.

Sub Test()
Dim rng As Range, C As Range
Dim rw As Long, txt As String
txt = "monkey"
rw = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(rw, 1))
Set C = rng.Find(What:=txt, LookIn:=xlValues, _
LookAt:=xlWhole)
rw = C.Row
MsgBox rw
End Sub

Be advised that I did respond to your other request in the
Miscellaneous newsgroup. I rewrote the code I gave you
yesterday. My experiments confirmed that it works at least
according to my understanding of the problem. I can only
respond in the evening because I work during the day.

Regards,
Greg

-----Original Message-----
I greatly appreciate your help. With a few tweaks, I
made afew changes
and am well on my way.

I have to admit I am pretty embarrassed that I was not
able to decipher
such a code myself. My attempts at this were much more
complicated.

Do you know a way that I can count how many cells are in
a column until
i reach a certain value (which is identified by a text
phrase).

Thanks again,

Brock


---
Message posted from http://www.ExcelForum.com/

.