Looping through blanks in a range

Nick Vittum

New Member
Joined
Apr 2, 2020
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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!
 
You could add a message box, asking if you want to reset, but that will appear everytime to run the macro.
I tried inserting
VBA Code:
        If Not UF_Categories.ActiveControl = UF_Categories.Go_Bttn Then Exit Sub
before the NxtCell.Select line, but it didn't work.
Wierdly, even if I unload the form altogether, when I reopen it, it remembers where it left off and keeps going from there. I can't understand how that could even be possible.
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
If the code is in a commandbutton click event in the userform, then it will start from the beginning everytime, however if you call the code then it will start of from the last cell found.
 
Upvote 0
If the code is in a commandbutton click event in the userform, then it will start from the beginning everytime, however if you call the code then it will start of from the last cell found.
Thank you. I moved the code directly to the button (it was part in a macro, before). What you say is true if I unload the form. Nothing short of that seems to interupt the process, however. I'm giving up for the day. I do surely appreciate the help.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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