Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Testing for a consistent value counting blanks | Excel Worksheet Functions | |||
copy range of cells with blanks then paste without blanks | Excel Programming | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Testing (unsuccessfully) for non-blanks | Excel Programming | |||
Testing a range for blanks | Excel Programming |