Finding not only the first blank cell, but all others as well in a column

[marcusja2002]

I've look just about everywhere and am having a tough time finding what I am looking for.

I've been able to find the last empty cell in a column:

iRow = ws.Cells(Rows.Count, 15).End(xlUp).Row + 1

I've been able to find code to find the first blank cell in a column:


Dim FirstCell As String
Dim i As Integer
FirstCell = "A1"
Range(FirstCell).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "" Then
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

What I cant find / figure out is end result to have iRow = the first empty cell in a column (15).

I also need the "next" button I create to be able to use the current cell chosen by iRow to move to the next blank cell. Each time I hit the next button it lets me move to the next blank cell. I dont want it to start over again at the first blank cell.

Any and all help is greatly appreciated.

Thank you in advance.

 

[Joe4]

I've been able to find the last empty cell in a column:

iRow = ws.Cells(Rows.Count, 15).End(xlUp).Row + 1

That is not quite right. What that code does is find the first empty cell after the last populated cell in that column (essentially, what it does is find the last populated cell, and then moves down one row from that).

That line of code is dynamic.

iRow = ws.Cells(Rows.Count, 15).End(xlUp).Row + 1

So if you keep running it over and over, it will find the last row and move down one row.
So if I understand you correctly. that is the only line of code you really need in your button.

 

[Fennek]

Hi,

as known from the F5-key to select blank cells, the vba version is

activesheet.usedrange.columns(1).specialcells(4)

it can be directly addressed with .formala = "=something"

of one-by-one accessed with a loop through the .arreas

regards

 

[marcusja2002]

Joe4

That code worked out great, the problem came up is I was informed the operators working on these may need to fill out some information and come back to it if not finished. The formula is looking for a sign off. If there are any blanks inbetween the last sign off and the top, those rows will never be found again. That is why I'm thinking I need to change the code to find the first blank instead, then somehow allow buttons to move blank to blank until they are all filled out.

That is not quite right. What that code does is find the first empty cell after the last populated cell in that column (essentially, what it does is find the last populated cell, and then moves down one row from that).

That line of code is dynamic.

[COLOR=#333333]iRow = ws.Cells(Rows.Count, 15).End(xlUp).Row + 1[/COLOR]

So if you keep running it over and over, it will find the last row and move down one row.
So if I understand you correctly. that is the only line of code you really need in your button.

 

[marcusja2002]

Fennek

I'm a vba noob, so I dont 100% understand what you said. I press the F5 key, special and blanks and it highlighted two blank cells in my column.

I'm not 100% sure how to activate the above code and in return, have that highlighted cell turn into a space holder for other formulas based on the row I'm currently in.

Hi,

as known from the F5-key to select blank cells, the vba version is

activesheet.usedrange.columns(1).specialcells(4)

it can be directly addressed with .formala = "=something"

of one-by-one accessed with a loop through the .arreas

regards

 

[Joe4]

OK, your original question was a bit confusing, so I couldn't really tell what you are after.
If you are looking for the first blank cell in a column (and selects it), try this code:

Sub FindFirstBlank()

    Dim col As Long
    Dim r As Long
    
'   Enter column number to apply this to
    col = 15
    
'   Check to see if either of first two cells is blank
    For r = 1 To 2
        If Cells(r, col) = "" Then
            Cells(r, col).Select
            Exit Sub
        End If
    Next r
    
'   If first blank not in first two rows, find it
    Cells(1, col).End(xlDown).Offset(1, 0).Select
    
End Sub

Note that you can use this for any column, simply by changing the col value at the top of code.

 

[Fluff]

Something like

Sub FirstBlank()

    Dim Rng As Range
    
    Set Rng = Columns(15).SpecialCells(xlBlanks)(1)
    Rng.Value = "Me"
End Sub

 

[marcusja2002]

Joe4

Sorry for the confusion, but I think I'm still confusing you. This code will find the first blank cell. I need the formula to find the first blank cell and then as long as the macro is running will continue to find the next blank cell every time I activate the macro.

OK, your original question was a bit confusing, so I couldn't really tell what you are after.
If you are looking for the first blank cell in a column (and selects it), try this code:

Sub FindFirstBlank()

    Dim col As Long
    Dim r As Long
    
'   Enter column number to apply this to
    col = 15
    
'   Check to see if either of first two cells is blank
    For r = 1 To 2
        If Cells(r, col) = "" Then
            Cells(r, col).Select
            Exit Sub
        End If
    Next r
    
'   If first blank not in first two rows, find it
    Cells(1, col).End(xlDown).Offset(1, 0).Select
    
End Sub

Note that you can use this for any column, simply by changing the col value at the top of code.

An example: Picture a column with numbers 1-17 doing down. If I delete 6 and 12 I want the code to first find the cell where the 6 was (now blank) then the next time I press the macro it will jump to the cell where 12 used to be.

Does that make a little more sence?

Thanks for your help

 

[Joe4]

What are you doing when you find the first blank cell?
Are you populating it at that point?
If so, then 12 will become the "first blank cell" after 6 is updated?

Maybe this code will help. Borrowing from Fluff's logic (which is really cool), this will find the first blank cell under the ActiveCell. So if you keep hitting it, it will keep going down to the next blank it finds. So just pick and cell and run it.

Sub FirstBlank()
   
    Dim r As Long
    Dim c As Long
    
'   Get row and column of activecell
    r = ActiveCell.Row
    c = ActiveCell.Column
    
    On Error GoTo err_fix
    Range(Cells(r + 1, c), Cells(Rows.Count, c)).SpecialCells(xlBlanks)(1).Select
    
    Exit Sub
    
err_fix:
    MsgBox "No more blank cells in middle of range"
    
End Sub

 

[Fluff]

Borrowing from Fluff's logic (which is really cool)

Why thank you kind sir.