0

The below code works fine to find the first empty cell in a given column (here column B). But what I need is a code to find the first blank cell in that column.

Sub macro1()
    Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
    Dim currentRowValue As String
    sourceCol = 2   'column B has a value of 2
    rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row
    'for every row, find the first blank cell and select it
    For currentRow = 1 To rowCount
        currentRowValue = Cells(currentRow, sourceCol).Value
        If IsEmpty(currentRowValue) Or currentRowValue = "" Then
            Cells(currentRow, sourceCol).Select
        End If
    Next
End Sub

Also, it should start looking from row 10 instead of row 1.

Can somebody rewrite this code to do this?

Stan
  • 937
  • 5
  • 15
  • 33
  • 1
    What's the difference between blank and empty cell? – Maco Aug 06 '14 at 09:15
  • yes, what is the difference between a blank cell and an empty cell? – Sunil Khiatani Aug 06 '14 at 09:56
  • possible duplicate of [Select first empty cell in column F starting from row 1. (without using offset )](http://stackoverflow.com/questions/14957994/select-first-empty-cell-in-column-f-starting-from-row-1-without-using-offset) –  Aug 06 '14 at 10:43

4 Answers4

2

Could something like this be what you're looking for:

Sub test()
Dim ws As Worksheet

Set ws = ActiveSheet

For Each cell In ws.Columns(2).Cells
    If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell
End Sub

This will run through each cell in column B in the active worksheet and select the first empty one it comes across. To set the sheet to a particular one Change Set ws = ActiveSheet to Set ws = Sheets("EnterSheetNameHere")

Or you could try using:

Sub test()
Dim ws As Worksheet

Set ws = ActiveSheet

For Each cell In ws.Columns(2).Cells
     If Len(cell) = 0 Then cell.Select: Exit For
Next cell
End Sub
Tom
  • 9,725
  • 3
  • 31
  • 48
0

My problem is solved by using the following code.

Sheets("sheet1").Select
Dim LR2 As Long, cell2 As Range, rng2 As Range
With Sheets("sheet1")
    LR2 = .Range("B" & Rows.Count).End(xlUp).Row
    For Each cell2 In .Range("B8:B" & LR2)
        If cell2.Value <> "" Then
            If rng2 Is Nothing Then
                Set rng2 = cell2
            Else
                Set rng2 = Union(rng2, cell2)
            End If
        End If
    Next cell2
    rng2.Select
End With
Stan
  • 937
  • 5
  • 15
  • 33
0

Just my two cents.

The function will look for the first encountered BLANK cell in a range, so it should work with columns and rows.

'Find first BLANK cell in a given range, returnt a range (one cell)
Function FirstBlank(ByVal rWhere As Range) As Range

    Dim vCell As Variant
    Dim answer As Range
    Set answer = Nothing

    For Each vCell In rWhere.Cells

        If Len(vCell.Formula) = 0 Then

            Set answer = vCell
            Exit For

        End If

    Next vCell

    Set FirstBlank = answer

End Function

And then do whatever you want with the cell.

Eilthalearin Kheru
  • 19
  • 4
0

Try this code to select the first empty cell below cell B10. But it requires B10 and B11 to be pre-occupied.

Range("B10").End(xlDown).Offset(1, 0).Select

or

Range("B100000").End(xlUp).Offset(1, 0).Select

user10287104
  • 1