Testing for blanks cells
 

I'm using this code

Cells.Range("AI2", Range("AI2").End(xlDown))

to select a range of data in a column but the trouble is, if the column is
empty the *whole* empty column is selected. Can this be modified such that,
if the column is empty then no selection is made.

Thanks very much IA.

Keith.

Testing for blanks cells
 

Keith, if you test, the cell after AI2 to see if it has something then you
can decide what to do. For example

dim cell as range
set cell = range("AI2")

if not isempty(cell.offset(1,0) then
Cells.Range("AI2", Range("AI2").End(xlDown)).Select
Else
'do something else
End if

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


"Keith Wilby" wrote in message
...
I'm using this code

Cells.Range("AI2", Range("AI2").End(xlDown))

to select a range of data in a column but the trouble is, if the column is
empty the *whole* empty column is selected. Can this be modified such
that, if the column is empty then no selection is made.

Thanks very much IA.

Keith.

Testing for blanks cells
 

"Bob Flanagan" wrote in message
. ..
Keith, if you test, the cell after AI2 to see if it has something then you
can decide what to do. For example

dim cell as range
set cell = range("AI2")

if not isempty(cell.offset(1,0) then
Cells.Range("AI2", Range("AI2").End(xlDown)).Select
Else
'do something else
End if


Cheers Bob, I'll take a look at that tomorrow.

Regards,
Keith.

Testing for blanks cells
 

Hi Keith,

You could wrap your procedure in an If...Then block so that it only
processes non-empty columns.

Also, to find the last row in a column you should work from the bottom
upward. If there are empty cells between the last row and first rows, use
another If...Then block to skip the blanks.

Example: (assumes you are iterating through each cell in the range)

Dim c As Range
Dim lLastRow as Long

lLastRow = Range("AI" & Rows.Count).End(xlUp).Row

If lLastRow 2 Then 'enter the procedure
For Each c In Range("AI2:AI" & lLastRow)
If Not c = "" Then 'continue

'do stuff

End If
Next c
End If

Note that I used an empty string for the test so that it includes cells with
formulas that return an empty string. (using IsEmpty will include such a cell)

HTH
Regards,
Garry

Testing for blanks cells
 

"GS" wrote in message
...
Hi Keith,

You could wrap your procedure in an If...Then block so that it only
processes non-empty columns.

Also, to find the last row in a column you should work from the bottom
upward. If there are empty cells between the last row and first rows, use
another If...Then block to skip the blanks.

Example: (assumes you are iterating through each cell in the range)

Dim c As Range
Dim lLastRow as Long

lLastRow = Range("AI" & Rows.Count).End(xlUp).Row

If lLastRow 2 Then 'enter the procedure
For Each c In Range("AI2:AI" & lLastRow)
If Not c = "" Then 'continue

'do stuff

End If
Next c
End If

Note that I used an empty string for the test so that it includes cells
with
formulas that return an empty string. (using IsEmpty will include such a
cell)

HTH
Regards,
Garry

Many thanks Garry.

Keith.