Find blank columns

al23
04-03-2003, 01:31 PM
I have a spreadsheet. The first row is all populated, it's the titles. The rest of each column is not necessarily populated. I want to go through all the titles and find the ones where their whole column is blank. Is there a simple way to do this? I am in middle but it's quite complicated and I feel there must be a better way that I am just not seeing.

Thanks

Wamphyri
04-03-2003, 02:07 PM
Yeah just check if End(xlDown).Row for that column = 65536 if it does it's empty

For I = 1 To UsedRange.End(xlToRight).Column
If Columns(I).End(xlDown).Row = 65536 Then
MsgBox "Column " & I & " is empty"
End If
Next

italkid
04-03-2003, 02:41 PM
Two little variants on Carl's code

For i = 1 To UsedRange.End(xlToRight).Column
If IsEmpty(Columns(i).End(xlDown)) Then
MsgBox "Column " & i & " is empty"
End If
Next

'Or like this

For i = 1 To UsedRange.End(xlToRight).Column
If WorksheetFunction.CountA(Columns(i).End(xlDown)) = 0 Then
MsgBox "Column " & i & " is empty"
End If
Next

al23
04-03-2003, 02:46 PM
Thanks.
But how do I loop through each column starting with the second row?

italkid
04-03-2003, 04:33 PM
If you want to start from row 2 you can count the blank cells in each
Column > if there are 65534 blank in a row its empty (65536 - the 2 rows in use)

Private Sub CommandButton1_Click()
Dim i As Integer

For i = 1 To UsedRange.End(xlToRight).Column
If WorksheetFunction.CountBlank(Columns(i)) = 65534 Then
MsgBox "Column " & i & " is empty"
End If
Next

End Sub

Mill
04-04-2003, 07:36 AM
Actually, this is how I would do it (slightly modifying Wamph's code):


For I = 1 To UsedRange.End(xlToRight).Column
'If CountA returns 1, then only one cell in that column has text or data in it
'We will assume that it is only the first row that contains text or data
If Application.WorksheetFunction.CountA(Columns(I)) = 1 Then
MsgBox "Column " & I & " is empty"
End If
Next I

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum