Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty on a range/array
Hi Benb,
To check if a range of cells is empty, try something like If Application.CountA(Range("A1:A20")) = 0 Then 'Range is empty Else 'Range is not empty End If To return the last populated cell in column A: Cells(Rows.Count, "A") and to return the following blank cell: Cells(Rows.Count, "A")(2) --- Regards, Norman "benb" wrote in message ... I need to find the last row of a column that still contains data. I cannot use Do While Not IsEmpty (or something similar) because there are blank cells mixed into the column. The best solution I can come up with is finding the cell at which the 20 or so cells above contain data and the 20 or so cells below are empty. The problem: IsEmpty does not seem to work in evaluating a range. How can I evaluate if Cells(1,1) to Cells(20,1) contain data while Cells(21,1) to Cells(40,1) are empty? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty on a range/array
Hi Benb,
Cells(Rows.Count, "A") should have been: Cells(Rows.Count, "A").End (xlUp) Similarly Cells(Rows.Count, "A")(2) should have been: Cells(Rows.Count, "A").End (xlUp)(2) --- Regards, Norman "Norman Jones" wrote in message ... Hi Benb, To check if a range of cells is empty, try something like If Application.CountA(Range("A1:A20")) = 0 Then 'Range is empty Else 'Range is not empty End If To return the last populated cell in column A: Cells(Rows.Count, "A") and to return the following blank cell: Cells(Rows.Count, "A")(2) --- Regards, Norman "benb" wrote in message ... I need to find the last row of a column that still contains data. I cannot use Do While Not IsEmpty (or something similar) because there are blank cells mixed into the column. The best solution I can come up with is finding the cell at which the 20 or so cells above contain data and the 20 or so cells below are empty. The problem: IsEmpty does not seem to work in evaluating a range. How can I evaluate if Cells(1,1) to Cells(20,1) contain data while Cells(21,1) to Cells(40,1) are empty? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty on a range/array
That's a great help. One question though: once I have found the last
populated cell using the code you suggested, how can I capture that row number in a variable? My purpose is I am using the row number in a For loop. Essentially I am asking it to go row by row in a table until it satisfies the condition or it reaches the last row of that one column. "Norman Jones" wrote: Hi Benb, Cells(Rows.Count, "A") should have been: Cells(Rows.Count, "A").End (xlUp) Similarly Cells(Rows.Count, "A")(2) should have been: Cells(Rows.Count, "A").End (xlUp)(2) --- Regards, Norman "Norman Jones" wrote in message ... Hi Benb, To check if a range of cells is empty, try something like If Application.CountA(Range("A1:A20")) = 0 Then 'Range is empty Else 'Range is not empty End If To return the last populated cell in column A: Cells(Rows.Count, "A") and to return the following blank cell: Cells(Rows.Count, "A")(2) --- Regards, Norman "benb" wrote in message ... I need to find the last row of a column that still contains data. I cannot use Do While Not IsEmpty (or something similar) because there are blank cells mixed into the column. The best solution I can come up with is finding the cell at which the 20 or so cells above contain data and the 20 or so cells below are empty. The problem: IsEmpty does not seem to work in evaluating a range. How can I evaluate if Cells(1,1) to Cells(20,1) contain data while Cells(21,1) to Cells(40,1) are empty? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty on a range/array
Hi Benb,
To store the row number of the last populated column A cell in a variable rw, try: Dim rw as Long rw = Cells(Rows.Count, "A").End (xlUp).row --- Regards, Norman "benb" wrote in message ... That's a great help. One question though: once I have found the last populated cell using the code you suggested, how can I capture that row number in a variable? My purpose is I am using the row number in a For loop. Essentially I am asking it to go row by row in a table until it satisfies the condition or it reaches the last row of that one column. "Norman Jones" wrote: Hi Benb, Cells(Rows.Count, "A") should have been: Cells(Rows.Count, "A").End (xlUp) Similarly Cells(Rows.Count, "A")(2) should have been: Cells(Rows.Count, "A").End (xlUp)(2) --- Regards, Norman "Norman Jones" wrote in message ... Hi Benb, To check if a range of cells is empty, try something like If Application.CountA(Range("A1:A20")) = 0 Then 'Range is empty Else 'Range is not empty End If To return the last populated cell in column A: Cells(Rows.Count, "A") and to return the following blank cell: Cells(Rows.Count, "A")(2) --- Regards, Norman "benb" wrote in message ... I need to find the last row of a column that still contains data. I cannot use Do While Not IsEmpty (or something similar) because there are blank cells mixed into the column. The best solution I can come up with is finding the cell at which the 20 or so cells above contain data and the 20 or so cells below are empty. The problem: IsEmpty does not seem to work in evaluating a range. How can I evaluate if Cells(1,1) to Cells(20,1) contain data while Cells(21,1) to Cells(40,1) are empty? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IsEmpty on a range/array | Excel Programming | |||
Do.....Loop until IsEmpty(....) | Excel Programming | |||
Help with IsEmpty | Excel Programming | |||
isempty | Excel Programming | |||
vba: isempty | Excel Programming |