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. :)
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
148
Office Version
  1. 2019
Platform
  1. Windows
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
 
Joined
Feb 15, 2018
Messages
15
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.
 
Joined
Feb 15, 2018
Messages
15

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,349
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about
VBA Code:
Sub Spaceballs()
Intersect(Range("C:C").SpecialCells(xlConstants).EntireRow, Range("E:E").SpecialCells(xlBlanks))(1).Select
End Sub
Beaten2it
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
@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).
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

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
Top