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
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
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