Looping through blanks in a range

[Nick Vittum]

I don't even know if loop is the proper term for what I want, but: I'm trying to come up with code that will allow my userform to find the first blank cell in Column B when I click a button, then skip to the next blank at the next click, and so on. I find lots of examples of code out there that will find the first blank in the column (or the last) but I can't find any that will cycle through the column. One I found that finds the first blank goes like this

Sub NextBlank()
    On Error Resume Next
    Dim xCell As Range
    For Each xCell In ActiveSheet.Columns(2).Cells
        If Len(xCell) = 0 Then
            xCell.Select
            Exit For
        End If
    Next
End Sub

Is it possible to adapt this to work through the whole column? Or does that "cycling through" require completely different code? (Also, if at all possible it needs to start on row 2, not 1. I can't even figure out how to do that with this code

Thanks!

 

[lrobbo314]

This seems to do the trick on the sample data below.

Book1
	B
1
2	x
3	x
4	x
5
6	x
7
8	x
9
10	x
11
12	x
13	x
14	x
15	x
16	x
17	x
18
19	x
20	x
21	x
22	x
Sheet1

Public col As Range
Public idx As Long

Private Sub Worksheet_Activate()
Set col = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
idx = 1
End Sub

Sub cycleThru()
If idx > col.Areas.Count Then idx = 1
col.Areas(idx).Select
idx = idx + 1
End Sub

 

[Fluff]

Another option

Sub NickVittum()
   Dim NxtCell As Range
   On Error Resume Next
   Set NxtCell = Range("B:B").SpecialCells(xlBlanks)(1)
   On Error GoTo 0
   If NxtCell Is Nothing Then Set NxtCell = Range("B" & Rows.Count).End(xlUp).Offset(1)
   NxtCell.Select
End Sub

 

[Nick Vittum]

Thank you both. I just got online, here on the south coast of Canada. I'll try these both out, and report back—

 

[Nick Vittum]

Well. . . I'm sad to say I couldn't make either of these codes do what I'm trying to accomplish. (You should both know that I'm fairly new to coding. So sometimes my attempts to adapt other people's code to my purposes are crude, and sometimes futile.)

Public col As Range
Public idx As Long

Private Sub Worksheet_Activate()
Set col = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
idx = 1
End Sub

Sub cycleThru()
If idx > col.Areas.Count Then idx = 1
col.Areas(idx).Select
idx = idx + 1
End Sub

@Lrobbo, I wasn't quite sure where to put your code, since what I'm trying for is for it to be behind a "Next Blank" button on the Userform that is the primary interface with the workbook (and as such, to be available on all sheets where the Userform is available). (a) First I tried putting the declarations and _Activate in the worksheet's code, and Sub cycleThru behind the button (even though I was skeptical) but of course it wouldn't work without the declarations. (b) Next I tried putting everything in the worksheet's code, and calling cycleThru from the button. But it just said "variable not defined." (c) Then I tried putting a button on the worksheet, just so I could test it out. This time, the result was that it selected the first blank in Column B, and would not budge from there (same result as every other coding I've tried so far). (d) Finally, I tried adapting the code so the whole thing could be a macro called by the button: same results as "c", above.

One odd thing I noticed: on the sheet I was testing this on there happened to be a group of blank cells down the column a bit. I tinkered with the range, to get it starting below B2, and then it moved down and selected the whole group of blank cells (this was true with both "c" and "d" above). But in no case would it move down again when the button was clicked a second time

Another option

Sub NickVittum()
   Dim NxtCell As Range
   On Error Resume Next
   Set NxtCell = Range("B:B").SpecialCells(xlBlanks)(1)
   On Error GoTo 0
   If NxtCell Is Nothing Then Set NxtCell = Range("B" & Rows.Count).End(xlUp).Offset(1)
   NxtCell.Select
End Sub

Thank you. It seemed like it ought to work—but, same results: it locked onto the first blank, and wouldn't move from there. I know what I need; I need it, each time a cell is selected, to use the new active cell as the new reference point and move on from there. I just don't know how to make that adaptation. I tried replacing ("B:B"), in the "SET" line, with (ActiveCell & ":B"). The result was that is skipped down to the first blank after the last filled cell. Then I tried replacing ("B" & Rows.Count) with (ActiveCell & ":" & Rows.Count) but that got no results at all. I even tried replacing both at once, but that just produced an error message.

So-oo.... sad and confused. But grateful for both your offerings. I'm sure, if I just understood a little more that I could adapt one or the other to get it to do what I need.

 

[Fluff]

If you don't put something in the first blank cell, then my code will not "move on" because it's still the first blank cell.
Why are you trying to find the 1st blank in col B?

 

[Nick Vittum]

If you don't put something in the first blank cell, then my code will not "move on" because it's still the first blank cell.
Why are you trying to find the 1st blank in col B?

Background: This workbook is for managing my finances. Sheets are initially filled with data from the Bank; then the Userform is used to categorize expenditures in Column B. Because the Bank data is often in a "code" all its own, it's sometimes difficult to tell at a glance what those expenditures actually were. In these cases, I need to refer to other resources to figure this out. In these cases (especially if 'other resources' means the website of someone I do business with) it's much more efficient to to browse down through the worksheet and do all expenditures from that particular vendor at once—which means leaving other blanks behind till later. Hence the need to "skip" down through the column.

So to answer your question: I'm not trying to find the first blank; I'm trying to find the next blank in Column B, working from whichever cell was last activated.

Thanks!

 

[Fluff]

Ok, in that case how about

Sub NickVittum()
   Static NxtCell As Range
   If NxtCell Is Nothing Then Set NxtCell = Range("B" & Rows.Count)
   Set NxtCell = Range("B:B").Find("", NxtCell, , , xlByRows, xlNext, , , False)
   NxtCell.Select
End Sub

 

[Nick Vittum]

O frabjous day! That is so very close. All I need now is a way to exit the sub if it's interrupted—say, by a manual selection. Because I notice that if I go back a couple of cells, for instance to make an edit I missed, and then click the button again, it resumes where it left off. I feel like I really ought to beable to know where/how to add an If ... Then Exit Sub line in there, but I guess my brain is a bit fried.

You have no idea how grateful I am.

 

[Fluff]

You could add a message box, asking if you want to reset, but that will appear everytime to run the macro.