Searching for "not blank"
 

I have a bit of code that searches a range object for the first blank cell:

Dim jswCell As Range

Range("B1:B280").Select
Set jswCell = Selection.Find(Empty)
Debug.Print jswCell.Row & "," & jswCell.Column

Once this is found, I would like it to start seraching from this blank cell,
continue down the column, and find the first "not blank" cell, regardless of
the value stored in that cell.

I have tried different ways of telling the .Find method to find the next
cell with a value, but everything I try gives errors. For instance,

Set jswCell = Selection.Find(Not Empty)

....will not find a "Not Empty" value. How should I code this?

Thanks!

Searching for "not blank"
 

can you use something like this:-

Selection.End(xlDown).Select

which is the ctrl+down arrow ??

Searching for "not blank"
 

Range("B1:B280").Select
Set jswCell = Selection.Find(Empty, After:=Range("B280"))
set rng = jswCell.End(xldown)
if rng.Row <= 280 then
rng.select
else
msgbox "Out of bounds"
end if


Building from Donna's suggestion
--
Regards,
Tom Ogilvy


wrote in message
oups.com...
can you use something like this:-

Selection.End(xlDown).Select

which is the ctrl+down arrow ??

Searching for "not blank"
 

"Joel" wrote in message
...
I have a bit of code that searches a range object for the first blank
cell:

Dim jswCell As Range

Range("B1:B280").Select
Set jswCell = Selection.Find(Empty)
Debug.Print jswCell.Row & "," & jswCell.Column

Once this is found, I would like it to start seraching from this blank
cell,
continue down the column, and find the first "not blank" cell, regardless
of
the value stored in that cell.

I have tried different ways of telling the .Find method to find the next
cell with a value, but everything I try gives errors. For instance,

Set jswCell = Selection.Find(Not Empty)

...will not find a "Not Empty" value. How should I code this?

Thanks!

Nice shortcut!
Any idea how to loop it?
Trying to improve/replace the following block which works....slowly.

For rowindex = finalrow To 1 Step -1
Select Case UCase$(Trim$(Cells(rowindex, 3).Value))
Case isblank: Rows(rowindex).Delete 'remove blanks and totals
Case "BILL": Rows(rowindex).Delete 'remove bills
Case "BILL PMT -CHECK"
If Cells(rowindex, 2).Value = "2000 · Accounts Payable" Then
Rows(rowindex).Delete
Case "DEPOSIT": Cells(rowindex, 8).Value = "DEPOSIT"
End Select
Next

Searching for "not blank"
 

"Joel" wrote in message
...
I have a bit of code that searches a range object for the first blank
cell:

Dim jswCell As Range

Range("B1:B280").Select
Set jswCell = Selection.Find(Empty)
Debug.Print jswCell.Row & "," & jswCell.Column

Once this is found, I would like it to start seraching from this blank
cell,
continue down the column, and find the first "not blank" cell, regardless
of
the value stored in that cell.

I have tried different ways of telling the .Find method to find the next
cell with a value, but everything I try gives errors. For instance,

Set jswCell = Selection.Find(Not Empty)

...will not find a "Not Empty" value. How should I code this?

Thanks!

try: (cursor should end up in the first cell containing data after the
first blank cell)
Range("B1:B280").Find(Empty).End(xlDown).select