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?

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?

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?

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?