Find 1st empty cell in a column

[bluegold]

Hi guys I have looked this up in the forum for answers but they all seem to return the below code as a solution.
This works fine to go the 1st blank at the end of a column but I have a few blank cells in the middle of the column of data and I want to go to that 1st empty cell, not the end. Any idea's ?

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

 

[JoeMo]

Maybe:

Sub test()
Dim FirstEmpty As Range
Set FirstEmpty = IIf(Range("A1") <> "", Range("A1").End(xlDown).Offset(1, 0), Range("A1"))
FirstEmpty.Select
End Sub

 

[bluegold]

Thanks for the quick reply Joe. Your code works as intended on its own but when I put your code in my full script below its not working because the condition "ElseIf lr1 > lr2 And lr2 < lr3 Then" is not being met because its not picking up that there is an empty cell in Column D, its just tallying up the end rows of all the columns.
By the way this script is intended to place the cursor at the 1st empty cell of the column depending on my conditions. Column A and AP are easy because there are never any empty cells so it can always place the cursor at 1st empty cell at the end of the column but only Column D contains empty cells and I want to place the cursor at the 1st empty cell within Column D. Does this make sense?

Dim lr1 As Long
Dim lr2 As Long
Dim lr3 As Long
Dim FirstEmpty As Range

Sheets("a").Select
lr1 = Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Cells(Rows.Count, "D").End(xlUp).Row
lr3 = Cells(Rows.Count, "AP").End(xlUp).Row

If lr1 = lr3 Then
Range("A" & Rows.Count).End(xlUp)(2).Select
ElseIf lr3 < lr2 And lr1 = lr2 Then
Range("AP" & Rows.Count).End(xlUp)(2).Select
ElseIf lr1 > lr2 And lr2 < lr3 Then
Set FirstEmpty = IIf(Range("D1") <> "", Range("D1").End(xlDown).Offset(1, 0), Range("D1"))
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
FirstEmpty.Select
Else
Range("A" & Rows.Count).End(xlUp)(2).Select
End If

 

[bluegold]

Just to demonstrate the below data should return the cursor to the 1st blankcell in Column D but instead it returns to 1st empty cell in Column AP because it thinks this following bolded condition is met "ElseIf lr3 < lr2 And lr1 = lr2 Then" when in fact it isn't

ColA---------ColB----------------ColC-------------ColD------ColAP

26/8/17	Sydney	Carlton	$1.06	138
26/8/17	Geelong	GWS	$1.86	103
26/8/17	Port Adelaide	Gold Coast	$1.06	135
27/8/17	Essendon	Fremantle		107
27/8/17	Richmond	St Kilda	$1.36
27/8/17	WCoast	Adelaide	$2.51

<tbody>
</tbody>

 

[lhartono]

Try this...

[COLOR=#0000ff]Sub Find_EmptyCells_In_UsedRange()

Dim rngData As Range
Dim c As Range

Set rngData = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeBlanks)
    For Each c In rngData
        '[/COLOR][COLOR=#a9a9a9]If c.Column = 1 Or c.Column = 4 Or c.Column = 42 Then  [/COLOR][COLOR=#a9a9a9]' to check spesific column[/COLOR][COLOR=#0000ff]
            Debug.Print c.Address
            c.Select
        [/COLOR][COLOR=#a9a9a9]'End If[/COLOR][COLOR=#0000ff]
    Next
Set rngData = Nothing

End Sub[/COLOR]

 

[bluegold]

Worked it out guys

Sheets("a").Select
lr1 = Range("A1").End(xlDown).Row
lr2 = Range("D1").End(xlDown).Row
lr3 = Range("AP1").End(xlDown).Row

If lr1 = lr2 And lr1 = lr3 Then
Range("A" & Rows.Count).End(xlUp)(2).Select
ElseIf lr3 < lr2 And lr1 = lr2 Then
Range("AP" & Rows.Count).End(xlUp)(2).Select
Else
Set FirstEmpty = IIf(Range("D1") <> "", Range("D1").End(xlDown).Offset(1, 0), Range("D1"))
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
FirstEmpty.Select
End If