Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
programming to select rows based on condition | Excel Discussion (Misc queries) | |||
Select rows based on keyword? | Excel Discussion (Misc queries) | |||
Need to select rows to average based on a value in a different column | New Users to Excel | |||
Select rows based on criteria | Excel Worksheet Functions | |||
Select rows and sort based on type | Excel Discussion (Misc queries) |