First NotBlank Column C and First Blank Column E

[Spaceballs_the_fanboy]

Hi folks,
I am looking for a VBA to search two column in a worksheet.
As the title suggests, I would like the code to find the first non-blank in Column B (it will be text, if that makes a difference)
Once it finds the first non-blank in Column B, it should look to see if Column E is Blank. If column E is blank the code should stop at the Row/Cell in Column E.

Is that possible? I am sure it is - you folks are amazing.

 

[Ezguy4u]

Just to get the ball rolling. I hope I got your instructions right. Now there are probably a better ways to do this. We just have to wait for the A students to weigh in.

Sub Search1()
Dim Row1 As Long
Dim LastRow As Long
Dim Trap As Boolean

Trap = False

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

For Row1 = 1 To LastRow
'Cells(Row1, 2).Select
    If Cells(Row1, 2) <> "" Or Trap = True Then
            Trap = True
        If Cells(Row1, 5) = "" Then
            Cells(Row1, 5).Select
            Exit Sub
        End If
    End If
Next Row1

End Sub

20-09-10 Blank.xlsm
	B	C	D	E
1				full
2				full
3	full			full
4				full
5	full			full
6	full
7	full
Sheet1

 

[Spaceballs_the_fanboy]

Just to get the ball rolling. I hope I got your instructions right. Now there are probably a better ways to do this. We just have to wait for the A students to weigh in.

Sub Search1()
Dim Row1 As Long
Dim LastRow As Long
Dim Trap As Boolean

Trap = False

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

For Row1 = 1 To LastRow
'Cells(Row1, 2).Select
    If Cells(Row1, 2) <> "" Or Trap = True Then
            Trap = True
        If Cells(Row1, 5) = "" Then
            Cells(Row1, 5).Select
            Exit Sub
        End If
    End If
Next Row1

End Sub

20-09-10 Blank.xlsm
	B	C	D	E
1				full
2				full
3	full			full
4				full
5	full			full
6	full
7	full
Sheet1

This is awesome! Thank you so much.

I have observed that Column C should not be blank, but when I use this VBA code it is finding the first blank Column E, whether or not Column C is blank or not.
I cannot seem to see why it is doing this with your code.

 

[Fluff]

Do you want to look at col C or col B?

 

[Spaceballs_the_fanboy]

Do you want to look at col C or col B?

I am wanting to find the first blank in Column E, only if Column C is NOT blank.

I have edited the code to replace "Row1, 2" to "Row1, 3" also I removed the OR Trap = True from "If Cells(Row1, 2) <> "" Or Trap = True Then"
This seems to have worked the way I expected it to.

 

[Rick Rothstein]

If you are trying to select the first blank cell in Column E when the cell on the same row in Column C is not blank, then you can also use this one-liner macro to do it as well...

Sub FindBlankColEwhenNonBlankColC()
  Intersect([C:C].SpecialCells(xlConstants, xlTextValues).EntireRow, [E:E].SpecialCells(xlBlanks))(1).Select
End Sub

 

[Fluff]

Ok, how about

Sub Spaceballs()
Intersect(Range("C:C").SpecialCells(xlConstants).EntireRow, Range("E:E").SpecialCells(xlBlanks))(1).Select
End Sub

Beaten2it

 

[Rick Rothstein]

@Spaceballs_the_fanboy,

You should use Fluff's shorter version of what I posted. I overthought the problem as well as rushed my answer and added an unnecessary second argument to the first SpecialCells function call in my code that could give incorrect results if you have a mixture of text and numbers in Column C. If you have only text there, then the two macros will always work the same (so you might as well use the shorter one anyway).

 

[Fluff]

Fluff's shorter version of what I posted

They're both 106 characters

 

[Rick Rothstein]

But yours is a shorter 106 characters. ?

Okay, to be technical, the OP should use yours because it excludes the second argument on that first SpecialCells function call irrespective of how each code line specifies the column references. If the OP wants to have a physically shorter code line, then this would be it...

Intersect([C:C].SpecialCells(xlConstants).EntireRow, [E:E].SpecialCells(xlBlanks))(1).Select