Determine if Column Populated

[kirkm]

In code, what would be the best / easiset way to
determine if a column has anything in any of
its rows (irrespective of how many rows) ?

I'll like to code a function that returns 0 if an entire column
is empty.

Thanks - Kirk

 

[Tom Hutchins]

This is pretty easy...

Public Function IsColEmpty(WhichCol As String) As Integer
IsColEmpty = Application.WorksheetFunction.CountA(Columns(WhichCol))
End Function

You would call it like this...

Sub AAAA()
MsgBox IsColEmpty("L")
End Sub

Hope this helps,

Hutch

 

[Leith Ross]

In code, what would be the best / easiset way to
determine if a column has anything in any of
its rows (irrespective of how many rows) ?

I'll like to code a function that returns 0 if an entire column
is empty.

Thanks - Kirk

Hello Kirk,

Here is a simple way in VBA. Just change the column letter to match
the range to check.

N = WorksheetFunction.CountA(Range("D"))

Sincerely,
Leith Ross

 

[Rick Rothstein \(MVP - VB\)]

Here is a robust function that will do what you asked plus a little bit
more....

Function LastFilledRow(ColumnNumber As Variant, Optional _
WorksheetID As Variant) As Long
Dim WS As Worksheet
On Error GoTo Whoops
If IsMissing(WorksheetID) Then
Set WS = ActiveSheet
Else
Set WS = Worksheets(WorksheetID)
End If
LastFilledRow = WS.Cells(WS.Rows.Count, ColumnNumber).End(xlUp).Row
If LastFilledRow = 1 And IsEmpty(WS.Cells(1, ColumnNumber)) Then
LastFilledRow = LastFilledRow - 1
End If
Exit Function
Whoops:
LastFilledRow = -1
End Function

The function's arguments allows you to specify the column by its number or
letter designation and, optionally, the worksheet by its caption or sheet
index number. The function returns the last column with anything in it. If
the column has nothing in it, it returns 0 (as per your original request)
and, if an invalid argument is supplied, it returns -1.

Rick

 

[kirkm]

Wow three replies!

Thank you all very much - interesting to see the variations.
I ended up applying Ricks as the last column filled could be useful.

Much appreciated... you chaps are great

Cheers - Kirk