Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if Column Populated
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if Column Populated
This is pretty easy...
Public Function IsColEmpty(WhichCol As String) As Integer IsColEmpty = Application.WorksheetFunction.CountA(Columns(Which Col)) End Function You would call it like this... Sub AAAA() MsgBox IsColEmpty("L") End Sub Hope this helps, Hutch "kirkm" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if Column Populated
On Apr 8, 6:56 pm, kirkm wrote:
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:D")) Sincerely, Leith Ross |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if Column Populated
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if Column Populated
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Last populated cell in a column | Excel Discussion (Misc queries) | |||
Last populated cell in a column | Excel Discussion (Misc queries) | |||
Referencing last populated cell in a column | Excel Worksheet Functions | |||
how can I find last populated cell in a row (or column) | Excel Programming | |||
how can I find last populated cell in a row (or column) | Excel Programming |