CODEWITHSUNDEEP
vba
user2530250
user2530250

Reputation: 17

VBA find first of consecutive empty cells in range

I've found this awesome code to find first empty cell in column from this link. However, this code does not work if I have 2 consecutive empty cells within the range with values. It will only select the 2nd empty cell, when I want the first. The consecutive cells could be anywhere within the range, first 2 or middle 2 or last 2. Also, it could be 3,4,5 consecutive cells so I can't use any row calculation formula. Would really appreciate if someone can advise me how to change the code.

Public Sub SelectFirstBlankCell()
Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String

sourceCol = 6   'column F has a value of 6
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

'for every row, find the first blank cell and select it
For currentRow = 3 To rowCount
    currentRowValue = Cells(currentRow, sourceCol).Value
    If IsEmpty(currentRowValue) Or currentRowValue = "" Then
        Cells(currentRow, sourceCol).Select
    End If
Next
End Sub

Also, just found that if I have multiple, non-consecutive empty rows that are within the range, in between data, it selects the last empty row as well (not the last row!)

Upvotes: 0

Views: 3508

Answers (1)

serakfalcon
serakfalcon

Reputation: 3531

The trick is to add an Exit For to break the loop if an empty cell is detected.

Also, if you want to make your code more extensible, I would suggest including sourceCol as a parameter rather defined in the sub. This allows you to create macros for any column

Public Sub SelectFirstFromF()
    Call SelectFirstBlankCell() 
End Sub

Public Sub SelectFirstFromB()
    Call SelectFirstBlankCell(2)
End Sub

Sub SelectFirstBlankCell(Optional sourceCol as Integer = 6)
Dim rowCount As Integer, currentRow As Integer
Dim currentRowValue As String

rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

'for every row, find the first blank cell and select it
For currentRow = 3 To rowCount
    currentRowValue = Cells(currentRow, sourceCol).Value
    If IsEmpty(currentRowValue) Or currentRowValue = "" Then
        Cells(currentRow, sourceCol).Select
        Exit For
    End If
Next
End Sub

Upvotes: 1

Related Questions