Excel vba to check to check if two columns are empty

subspace3 · #1 07-03-2015, 05:14 AM

Hi all,

Wondering if someone can help. I need to check if two Columns a and b in a row selection are empty.

The selection is done via the user selecting the rows and want it to work no matter how many rows are selecting. Thinking of having a message box for each saying row x column x is empty please correct.

Any ideas?

I was thinking along the lines of
If application.selection
And then a range then = " "

But no idea where to start.

macropod · #2 07-05-2015, 12:54 AM

For a basic report, you could use code like:

Code:

Dim lRow As Long, bEmpty As Boolean
lRow = ThisWorkbook.ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
bEmpty = True
With Selection
  If .Rows(1).Row < lRow Then
    If ThisWorkbook.ActiveSheet.Range("A" & .Rows(1).Row & ":B" & .Rows(.Rows.Count).Row).SpecialCells(xlCellTypeBlanks).Count _
      < .Rows.Count * 2 Then bEmpty = False
  End If
  If bEmpty = True Then
    MsgBox "All rows in columns A & B in the selected rows are empty."
  Else
    MsgBox "At least one row in columns A & B iin the selected rows is not empty."
  End If
End With

subspace3 · #3 07-06-2015, 09:32 AM

Awesome, thanks. Not exactly sure how this works with the first two if statements but I've tested it and works fine.

Throws an error if I select a row by itself where A and B are not empty but I'll have a mess around see what I can figure out. No point putting this in if I don't learn anything or know how it works!

macropod · #4 07-06-2015, 02:13 PM

I'm not seeing any errors in that scenario. Are you sure you're using the revised version of the code, which I posted about 20mins after my initial reply?

subspace3 · #5 07-09-2015, 09:37 AM

Thanks for the reply. I'm
Sure I got the updated version but I'll double check tonight.
It comes up with a pop up saying cells not found error 1004 I think it is.

Is it possible to give a little explanation of how it works?

macropod · #6 07-09-2015, 04:45 PM

Basically, all the macro does is to compare the count of empty cells in columns A & B for the rows selected against twice the number of rows. Quite simple, really. A slightly different approach is required once you're past the last used row on the workbook, but in that case one can safely assume columns A & B are empty.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Check boxes that insert data from Excel	spc94	Word VBA	1	06-22-2015 01:46 PM
Multiple Check In/check Out Times	big0	Excel	4	09-19-2013 05:02 AM
cannot check/uncheck check box but added check box	learn2office	Word	1	11-27-2012 02:02 AM
Link word check box to access check box	Mrkieth	Word	4	01-30-2012 06:43 AM
Need Help with Using a Check Box	Nick9589	Excel	1	05-01-2010 09:29 AM