First Non-Blank Row# between Multiple Columns

[bs0d]

So I've seen this question a lot, but I haven't found a solution that applies to multiple columns.

I'm looking to obtain the row number that represents the first cell that contains any data, search between a pre-defined range of columns, for instance B:G.

Most of what I find applies to one column only, like this:

Columns("A").Find("*", Cells(Rows.Count, "A"), xlValues, xlWhole, , xlNext).Address

 

[brian.wethington]

Would look something like this:

Sub Test1()
Dim Rng As Range

Set Rng = Columns("B:G").Find("*", Cells(Rows.Count, "B"), xlValues, xlWhole, , xlNext)
If Not Rng Is Nothing Then Debug.Print Rng.Address
End Sub

 

[BobUmlas]

Sub Test1()
MsgBox Range("B:G").Find("*", searchdirection:=xlPrevious).Row + 1
End Sub

 

[bs0d]

Would look something like this:

Sub Test1()
Dim Rng As Range

Set Rng = Columns("B:G").Find("*", Cells(Rows.Count, "B"), xlValues, xlWhole, , xlNext)
If Not Rng Is Nothing Then Debug.Print Rng.Address
End Sub

Hey just checking, should this be:

Sub Test1()
Dim Rng As Range

Set Rng = Columns("B:G").Find("*", Cells(Rows.Count, "B:G"), xlValues, xlWhole, , xlNext)
If Not Rng Is Nothing Then Debug.Print Rng.Address
End Sub

 

[brian.wethington]

Hey just checking, should this be:

Sub Test1()
Dim Rng As Range

Set Rng = Columns("B:G").Find("*", Cells(Rows.Count, "B:G"), xlValues, xlWhole, , xlNext)
If Not Rng Is Nothing Then Debug.Print Rng.Address
End Sub

No, in the find method you need to search after or before a particular cell sometimes. In this case you are trying to go to the very end of your range so that you can find the next used cell.

Cells(Rows.Count, "B")

refers to cell B1048576. There could be an argument made that I really should have used

Cells(Rows.Count, "G")

to use G1048576 because if data is found in any of the row 1048576 and in the columns B:G then it will return that, but I assumed there is very little chance that the last row will contain any data.

 

[brian.wethington]

Sub Test1()
MsgBox Range("B:G").Find("*", searchdirection:=xlPrevious).Row + 1
End Sub

To be clear, this does not find the first cell with an entry in a range, it finds the last cell used in a range and then returns the next row.

 

[bs0d]

Great, I appreciate the feedback. I guess while I have your ear, why use

If Not Rng is Nothing

Is that faster code than saying if Rng = vbNullString? or <> "" or empty(Rng) ? Just curious

and what's with the , , in the .find function? just no parameter provided?

 

[bs0d]

Crud... any way to omit 1st row???

 

[bs0d]

This seems to ring the bell:

Sub Test1()
Dim Rng As Range

Set Rng = Columns("B:G").Find("*", Cells(2, "B:G"), xlValues, xlWhole, , xlNext)
If Not Rng Is Nothing Then Debug.Print Rng.Address
End Sub

 

[brian.wethington]

Great, I appreciate the feedback. I guess while I have your ear, why use

If Not Rng is Nothing

Is that faster code than saying if Rng = vbNullString? or <> "" or empty(Rng) ? Just curious

Okay, first we are testing to make sure an instance was found. We do this before we apply a .address or .row (or whatever assignment you are trying to make) to ensure we don't throw errors. Rng <> Empty and Rng <> vbNullString will error for the same reasons that Rng.Address would error because it is looking for the value of the cell found (Rng.Value); since Rng doesn't exist, it will cause an error in the code.

and what's with the , , in the .find function? just no parameter provided?

The find method has several optional modifiers, the only required one being what it is you are looking for. There are 2 ways to handle the modifiers if you want to use some but not all of them. You can use commas to hold each position or you can name each modifier. An example of naming each modifier would be

sRange.Find(what:=lRange.Value,  After:=oSheet.Cells(1, 1), Lookat:=xlWhole, MatchCase:=True)

Crud... any way to omit 1st row???

Set Rng = Columns("B:G").Find("*", Cells(1, "G"), xlValues, xlWhole, , xlNext)

Essentially you are using the last cell in row 1 in your range and starting from there to find the next used cell.