Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

VBA Excel ? - How do i identify empty columns

I need to figure out what the first column is in spreadsheets that has no data. I think I can remember how to find the last active cell, but what I need is the first column which has no data in any cell in that column.

What is the least brute force method to do this?
0
quantz
Asked:
quantz
  • 2
  • 2
1 Solution
 
calacucciaCommented:
Hi Quantz,

Closely related to your question in Win95 area..

What about the code I posted in that question ?

Sub FindEmptyColumns()
Dim EmptyRange As Range, Col As Range
Set EmptyRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks)
For Each Col In ActiveSheet.UsedRange.Columns
If Intersect(Col, EmptyRange).Rows.Count = Col.Rows.Count Then
MsgBox ("Column " & Col.Column & " is empty !")
End If
Next Col
End Sub

To get stop code at first empty column, change to:
Sub FindEmptyColumns()
Dim EmptyRange As Range, Col As Range
Set EmptyRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks)
For Each Col In ActiveSheet.UsedRange.Columns
If Intersect(Col, EmptyRange).Rows.Count = Col.Rows.Count Then
MsgBox ("Column " & Col.Column & " is the first empty column in the used range!")
Exit Sub
End If
Next Col
End Sub

Calacuccia
0
 
quantzAuthor Commented:
This looked like it would work as is.  But it failed when the text file I "imported" to format had no empty cells in it before the end of the file. It gave me an error message "No empty cells".  So I got rid of the "special cells" part of the code, just selecting "Used Range" and put an integer as a counter in the "For each Col in " loop.  Works fine.
0
 
quantzAuthor Commented:
Calacuccia:
Hmm, so thats what happened to my first posting.... ended up in Win5...
Thanks
0
 
calacucciaCommented:
Here's the URL for your win95 question. I suppose you'll want to delete that one.

http://www1.experts-exchange.com/Computers/Operating_Systems/Windows/Win95/Q_10331770.html

Regarding your problem, you could also use this little modified code (traps the error message 'No empty cells found', displays a little message box and ends the procedure)

Sub FindEmptyColumns()
Dim EmptyRange As Range, Col As Range
On Error GoTo ErrorHandler
Set EmptyRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
For Each Col In ActiveSheet.UsedRange.Columns
If Intersect(Col, EmptyRange).Rows.Count = Col.Rows.Count Then
MsgBox ("Column " & Col.Column & " is empty !")
End If
Next Col
ErrorHandler:
MsgBox ("No empty columns in the used range of your spreadsheet !")
End Sub

Cheers
Calacuccia
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now