Finding the next empty cell.

U

Unknown_User

Hi all,

A successful night so far as my first question was answered withi
seconds!

I want to populate the next available empty cell in a column with th
results of a VB script that returns a string.

Can anyone tell me how to locate the next empty cell?

Thanks
 
P

pikus

lrow = ActiveSheet.UsedRange.Row + _
ActiveSheet.UsedRange.Rows.Count

Will give the first empty row. Replace the word "Row" with "Column
and get the first empty column. Is that good? - Piku
 
T

Tom Ogilvy

It will give the next row after the last row Excel considers to be used.
Considering a row to be used doesn't necessarily have anything to do with a
particular cell being empty or not although in many situations it will give
the desired result - quite unreliable for sheets with columns of varying
lengths and so forth.
 
U

Unknown_User

Thanks.

Sorry to be so dumb. Will this return a column/row value?

Ideally I want to identify the next cell in, lets say, column A, an
use that value to identify the cell to populate;

Worksheets("Sheet1").Range([next avalable cell]).Value = string

Hopefully I am making sense....
 
T

Tom Ogilvy

Assume column A
Cells(1,1).End(xldown)(2).Value = "String"

If A1 and or A2 could be blank

Dim rng as Range
if isempty(Range("A1")) then
set rng = Range("A1")
elseif isempty(Range("A2")) then
set rng = Range("A2")
else
set rng = Range("A1").End(xldown)
End if
set rng = rng(2)

if there will never be data below the "next empty cell"

set rng =cells(rows.count,1).End(xlup)
if not isempty(rng) then _
set rng = rng(2)
 
R

Ron de Bruin

If A1 and or A2 could be blank

Hi Tom is this a good option ?

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


Tom Ogilvy said:
Assume column A
Cells(1,1).End(xldown)(2).Value = "String"

If A1 and or A2 could be blank

Dim rng as Range
if isempty(Range("A1")) then
set rng = Range("A1")
elseif isempty(Range("A2")) then
set rng = Range("A2")
else
set rng = Range("A1").End(xldown)
End if
set rng = rng(2)

if there will never be data below the "next empty cell"

set rng =cells(rows.count,1).End(xlup)
if not isempty(rng) then _
set rng = rng(2)
Click to expand...
 
T

Tom Ogilvy

Looks good to me Ron.

--
Regards,
Tom Ogilvy

Ron de Bruin said:
If A1 and or A2 could be blank
Click to expand...

Hi Tom is this a good option ?

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