Macro VBA: Check If Column is empty

[harky]

I had data from

A to T

I want to check the entire column of D, if there is a empty cell. Marco will aborted.
MsgBox "Pls ensure Col D is not empty"

 

[Akuini]

You mean if entire col D is empty then show a message box?
Try this:

If WorksheetFunction.CountA(Range("D:D")) = 0 Then MsgBox "Pls ensure Col D is not empty"

 

[jasonb75]

This should do what you need, although I would suggest using a different column in line 2 to one that will have data even if column D has been left empty, otherwise it will not pick up if the last row is empty.
Add this to the beginning of your existing code.

Dim dRng As Range, lRow As Long
lRow = Cells(Rows.Count, "D").End(xlUp).Row
On Error Resume Next
    Set dRng = Range("D1:D" & lRow).SpecialCells(xlBlanks)
On Error GoTo 0
If dRng Is Nothing Then
    MsgBox "Pls ensure Col D is not empty", vbExclamation
    Exit Sub
End If

 

[harky]

Check Col D if there is a empty cell

You mean if entire col D is empty then show a message box?

Try this:

If WorksheetFunction.CountA(Range("D:D")) = 0 Then MsgBox "Pls ensure Col D is not empty"[/FONT]

[FONT=Verdana]

 

[harky]

Not sure if i say wrongly??
I want to check if Col D had empty cell.

I had input data from D1 to D12.
Since all Column is not empty, i suppose the marco will not abort.

But if there is 1 empty cell in Column D, marco will aborted.

This should do what you need, although I would suggest using a different column in line 2 to one that will have data even if column D has been left empty, otherwise it will not pick up if the last row is empty.
Add this to the beginning of your existing code.

Dim dRng As Range, lRow As Long
lRow = Cells(Rows.Count, "D").End(xlUp).Row
On Error Resume Next
    Set dRng = Range("D1:D" & lRow).SpecialCells(xlBlanks)
On Error GoTo 0
If dRng Is Nothing Then
    MsgBox "Pls ensure Col D is not empty", vbExclamation
    Exit Sub
End If

 

[harky]

This code dont work.

It still run macro where there is empty cell on Col D

You mean if entire col D is empty then show a message box?
Try this:

If WorksheetFunction.CountA(Range("D:D")) = 0 Then MsgBox "Pls ensure Col D is not empty"

 

[jasonb75]

The code that I've provided for you will check to see if there are any empty cells above the last row of data in column D.

The problem with it would be if the last data entry in column D was in D20, but other columns had data in row 21. In this case D21 would not be seen as empty, which is why I suggested changing the column in the second line so that it can be compared to a column that will always have data in every row.

 

[harky]

oh........... ok..
i understand now.

So i had to change the


Set dRng = Range("D1:D" & lRow).SpecialCells(xlBlanks)

The code that I've provided for you will check to see if there are any empty cells above the last row of data in column D.

The problem with it would be if the last data entry in column D was in D20, but other columns had data in row 21. In this case D21 would not be seen as empty, which is why I suggested changing the column in the second line so that it can be compared to a column that will always have data in every row.

 

[harky]

Thanks,
I change the 2nd line but

there is no empty cell on D. The marco is not running.

Dim dRng As Range, lRow As Long
lRow = Cells(Rows.Count, "D").End(xlUp).Row
On Error Resume Next
    Set dRng = Range("J1:J" & lRow).SpecialCells(xlBlanks)
On Error GoTo 0
If dRng Is Nothing Then
    MsgBox "Pls ensure Col D is not empty", vbExclamation
    Exit Sub
End If

The code that I've provided for you will check to see if there are any empty cells above the last row of data in column D.

The problem with it would be if the last data entry in column D was in D20, but other columns had data in row 21. In this case D21 would not be seen as empty, which is why I suggested changing the column in the second line so that it can be compared to a column that will always have data in every row.

 

[jasonb75]

You have changed the 4th line, not the 2nd!

Dim dRng As Range, lRow As Long
[U]lRow = Cells(Rows.Count, "J").End(xlUp).Row[/U]
On Error Resume Next
    Set dRng = Range("D1:D" & lRow).SpecialCells(xlBlanks)
On Error GoTo 0
If dRng Is Nothing Then
    MsgBox "Pls ensure Col D is not empty", vbExclamation
    Exit Sub
End If