first non blank cell

[guest]

Hello, on clue on how to find first non blank cell in the columns selected.
For example; if user selects Column A and Column B I need to find first non
blank cell only in the column A and B.

thanks.

 

[Ron de Bruin]

Hi guest

Try this

Sub test()
On Error GoTo BodemUp
Columns("A:B").Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select
Exit Sub
BodemUp: Cells(Rows.Count, "A").End(xlUp)(2).Select
End Sub

 

[Héctor Miguel]

hi, !

... on clue on how to find first non blank cell in the columns selected. For example;
if user selects Column A and Column B I need to find first non blank cell only in the column A and B.

for the first non blank cell in selected range... maybe something like...

On Error GoTo EmptyRange
With Selection ' Columns("a:b")
.Find("*", .Cells(.Rows.Count, .Columns.Count), xlValues, xlWhole, xlByRows).Select
Exit Sub
EmptyRange: MsgBox "It's an empty range !": .Cells(1).Select
End With

hth,
hector.

 

[Rick Rothstein \(MVP - VB\)]

Not sure what you mean by "find"... this code will create a selection
composed of the first blank cell in each column of the original selection
(this original selection does not have to be the whole columns... you can
simply one or more cells in each column before running the code).

Dim C As Range
Dim Result As Range
For Each C In Selection.Columns
If Result Is Nothing Then
Set Result = C.Offset(1 - C.Row, 0).End(xlDown)
Else
Set Result = Union(Result, C.Offset(1 - C.Row, 0).End(xlDown))
End If
Next
Result.Offset(1, 0).Select

If you need to do something other than select the cells, the
Result.Offset(1, 0) range will contain all first blank cells in each
column... simply do something else besides selecting them.

Rick