First NotBlank Column C and First Blank Column E

Joined
Feb 15, 2018
Messages
15
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. :)
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.

VBA Code:
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
BCDE
1full
2full
3fullfull
4full
5fullfull
6full
7full
Sheet1
 
Upvote 0
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.

VBA Code:
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
BCDE
1full
2full
3fullfull
4full
5fullfull
6full
7full
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.
 
Upvote 0
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.
 
Upvote 0
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...
VBA Code:
Sub FindBlankColEwhenNonBlankColC()
  Intersect([C:C].SpecialCells(xlConstants, xlTextValues).EntireRow, [E:E].SpecialCells(xlBlanks))(1).Select
End Sub
 
Upvote 0
Ok, how about
VBA Code:
Sub Spaceballs()
Intersect(Range("C:C").SpecialCells(xlConstants).EntireRow, Range("E:E").SpecialCells(xlBlanks))(1).Select
End Sub
Beaten2it
 
Upvote 0
@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).
 
Upvote 0
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...
VBA Code:
Intersect([C:C].SpecialCells(xlConstants).EntireRow, [E:E].SpecialCells(xlBlanks))(1).Select
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top