#1
|
|||
|
|||
Excel vba to check to check if two columns are empty
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. |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] Last edited by macropod; 07-05-2015 at 01:13 AM. Reason: Enhanced code |
#3
|
|||
|
|||
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! |
#4
|
||||
|
||||
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?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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? |
#6
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
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 |