Macro VBA: Check If Column is empty

harky

Active Member
Joined
Apr 8, 2010
Messages
405
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
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"
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You mean if entire col D is empty then show a message box?
Try this:

Code:
If WorksheetFunction.CountA(Range("D:D")) = 0 Then MsgBox "Pls ensure Col D is not empty"
 
Upvote 0
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.
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
 
Upvote 0
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.
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
 
Upvote 0
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:

Code:
If WorksheetFunction.CountA(Range("D:D")) = 0 Then MsgBox "Pls ensure Col D is not empty"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Thanks,
I change the 2nd line but

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


Code:
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.
 
Last edited:
Upvote 0
You have changed the 4th line, not the 2nd!
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top