PDA

View Full Version : How to check the data column and empty column???



sankartalam
05-22-2008, 12:15 AM
Hi all,

I want to run the loop using columns A,B,C,...Z columns and want to check which column is empty and which is non empty.

for ex:-

A B C D E
s s s s
d d d d

In the above structure 'E' is not having any data. In my for loop the check condition shoul return 'E'(Empty data Column) as my output.

Could anyone help me please

Charlize
05-22-2008, 12:50 AM
For excel 2003.Sub test_column_if_empty()
MsgBox Application.WorksheetFunction.CountBlank(Range("E:E"))
'if you need 65534 rows to be blank because E1 has label
If Application.WorksheetFunction.CountBlank(Range("E:E")) - 1 = 65534 Then
MsgBox "Column E has no data", vbInformation
Else
MsgBox "Column E has data in it", vbInformation
End If
End SubCharlize

sankartalam
05-22-2008, 05:10 AM
Hi Charlize thanks alot for your kind reply...


The code which you sent to me is only for checking the 'E' column. But my requirement is loop has to run Starting from the column A to Z
It has to check the for the column A 1st, if it is having data then the loop variable goes for the next column ('B'), if column 'B' is also having data then it has to check for the column 'C'. finally whenever if it finds empty column first then it has to return that column name.
In my previous post i used sample structure, In which column E is having no data.

david000
05-22-2008, 09:56 PM
Sub FindLastColumn()
Dim LCol As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
LCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
ActiveSheet.Columns(LCol).Offset(, 1).Select
End Sub