Hi newsgroup,

I hope you can help me with some VBA challenge:

I do have a range named "demand" starting at "B15" and ending somewhere
variably - it has one column and around 800 rows...

My problem is that I need to know which cell of the range is the first empty
cell - therefore it would be perfect if excel can give me the relative
reference, so to say : the xxxth cell counted from the top cell is empty...

Does anyone know a possible solution?

Of course I could use a loop but it would be very bad because the macro is
long enough and if he checks every cell it would take some more minutes...
and time is money ;o)

Very heavy thanks in advance and best regards,


Markus

Hi Markus

firstEmptyCell = Range("B15").End(xlDown).Offset(1,0).Address

returns the address of that empty cell. But beware. it throws an error if
B16 is empty, so best to test for that explicitly and if so, bypass that
statement.

--
HTH

Bob Phillips

"Markus Scheible" wrote in message
...
Hi newsgroup,

I hope you can help me with some VBA challenge:

I do have a range named "demand" starting at "B15" and ending somewhere
variably - it has one column and around 800 rows...

My problem is that I need to know which cell of the range is the first
empty
cell - therefore it would be perfect if excel can give me the relative
reference, so to say : the xxxth cell counted from the top cell is
empty...

Does anyone know a possible solution?

Of course I could use a loop but it would be very bad because the macro is
long enough and if he checks every cell it would take some more minutes...
and time is money ;o)

Very heavy thanks in advance and best regards,


Markus

Here is a function that does it You can get the logic from there.

Sub Main()
MsgBox ShowEmpty(Range("Demand"))
End Sub

Function ShowEmpty(rng1 As Range)
Dim rng As Range
'Application.Volatile
Set rng = Nothing
'Set rng1 = Range("demand")
If IsEmpty(rng1(1)) Then
Set rng = rng1(1)
ElseIf IsEmpty(rng1(2)) Then
Set rng = rng1(2)
Else
Set rng = rng1(1).End(xlDown)(2)
End If
' now rng holds a reference to the first empyt cell
' rng.row gives the row
' rng.row - rng1(1).row +1 gives the nth number
ShowEmpty = "absolute row: " & rng.Row & vbNewLine _
& "nth row: " & rng.Row - rng1(1).Row + 1
End Function

--
Regards,
Tom Ogilvy




"Markus Scheible" wrote in message
...
Hi newsgroup,

I hope you can help me with some VBA challenge:

I do have a range named "demand" starting at "B15" and ending somewhere
variably - it has one column and around 800 rows...

My problem is that I need to know which cell of the range is the first
empty
cell - therefore it would be perfect if excel can give me the relative
reference, so to say : the xxxth cell counted from the top cell is
empty...

Does anyone know a possible solution?

Of course I could use a loop but it would be very bad because the macro is
long enough and if he checks every cell it would take some more minutes...
and time is money ;o)

Very heavy thanks in advance and best regards,


Markus

Hi Bob,

firstEmptyCell = Range("B15").End(xlDown).Offset(1,0).Address

returns the address of that empty cell. But beware. it throws an error if
B16 is empty, so best to test for that explicitly and if so, bypass that
statement.


heavy thanks :o)

Best regards,

Markus

Hi Tom,

heavy thanks to you as well :o)

Have a nice day!


Markus



Function ShowEmpty(rng1 As Range)
Dim rng As Range
'Application.Volatile
Set rng = Nothing
'Set rng1 = Range("demand")
If IsEmpty(rng1(1)) Then
Set rng = rng1(1)
ElseIf IsEmpty(rng1(2)) Then
Set rng = rng1(2)
Else
Set rng = rng1(1).End(xlDown)(2)
End If
' now rng holds a reference to the first empyt cell
' rng.row gives the row
' rng.row - rng1(1).row +1 gives the nth number
ShowEmpty = "absolute row: " & rng.Row & vbNewLine _
& "nth row: " & rng.Row - rng1(1).Row + 1
End Function

--