Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Excel vba to check to check if two columns are empty Excel vba to check to check if two columns are empty
Register FAQ Mark Forums Read

Reply
 
Thread Tools Display Modes
  #1  
Old 07-03-2015, 05:14 AM
subspace3 subspace3 is offline Excel vba to check to check if two columns are empty Windows 7 64bit Excel vba to check to check if two columns are empty Office 2010 64bit
Novice
Excel vba to check to check if two columns are empty 		 
Join Date: Oct 2014
Posts: 8
subspace3 is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 07-05-2015, 12:54 AM
macropod's Avatar
macropod macropod is offline Excel vba to check to check if two columns are empty Windows 7 64bit Excel vba to check to check if two columns are empty Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
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
Reply With Quote
  #3  
Old 07-06-2015, 09:32 AM
subspace3 subspace3 is offline Excel vba to check to check if two columns are empty Windows 7 64bit Excel vba to check to check if two columns are empty Office 2010 64bit
Novice
Excel vba to check to check if two columns are empty 		 
Join Date: Oct 2014
Posts: 8
subspace3 is on a distinguished road
Default
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!
Reply With Quote
  #4  
Old 07-06-2015, 02:13 PM
macropod's Avatar
macropod macropod is offline Excel vba to check to check if two columns are empty Windows 7 64bit Excel vba to check to check if two columns are empty Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
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]
Reply With Quote
  #5  
Old 07-09-2015, 09:37 AM
subspace3 subspace3 is offline Excel vba to check to check if two columns are empty Windows 7 64bit Excel vba to check to check if two columns are empty Office 2010 64bit
Novice
Excel vba to check to check if two columns are empty 		 
Join Date: Oct 2014
Posts: 8
subspace3 is on a distinguished road
Default
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?
Reply With Quote
  #6  
Old 07-09-2015, 04:45 PM
macropod's Avatar
macropod macropod is offline Excel vba to check to check if two columns are empty Windows 7 64bit Excel vba to check to check if two columns are empty Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
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]
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel vba to check to check if two columns are empty 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
Excel vba to check to check if two columns are empty 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:12 PM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft