Find first blank cell
 

Hi,

I have two sheets of data I would like to merge. The problem is that both
are variable. I would like to past the second sheet of data below the first.
Can you please let me know the code to find the first blank cell in the first
sheet?

Thanks
Rui

Find first blank cell
 

Hi Rui,

Try:

Dim NextCell As Range

Set NextCell = Sheets("Sheet1").Cells(Rows.Count, 1). _
End(xlUp)(2)

---
Regards,
Norman



"Rui" wrote in message
...
Hi,

I have two sheets of data I would like to merge. The problem is that both
are variable. I would like to past the second sheet of data below the
first.
Can you please let me know the code to find the first blank cell in the
first
sheet?

Thanks
Rui

Find first blank cell
 

Norman:
I see this all too often, that is the "(2)" at the very end of your
code.
What does it instruct VB to do?
TIA,


"Norman Jones" wrote in message
...
Hi Rui,

Try:

Dim NextCell As Range

Set NextCell = Sheets("Sheet1").Cells(Rows.Count, 1). _
End(xlUp)(2)

---
Regards,
Norman



"Rui" wrote in message
...
Hi,

I have two sheets of data I would like to merge. The problem is that
both
are variable. I would like to past the second sheet of data below the
first.
Can you please let me know the code to find the first blank cell in the
first
sheet?

Thanks
Rui

Find first blank cell
 

Jim May wrote:
Norman:
I see this all too often, that is the "(2)" at the very end of your
code.
What does it instruct VB to do?
TIA,

See Item Property (Range Object) in Microsoft Visual Basic Help. One
thing the Help does not clarify is that the "Item" can be omitted; i.e.,
Range("A1:A10").Item(1) can be expressed Range("A1:A10")(1). Another
slight error in the Help is that it indicates that if the name of the
relevant range is "Range", then Range.Item(2) will return the cell
immediately to the right of the upper left cell; that's true only if
Range has more than one column; if it does not, then you can refer to
the cell immediately to the right of the upper left cell with
Range.Item(1,2) [or, as mentioned above, Range(1,2)]; ositive single
indexing can be used to refer only to cells within and directly below
the specified range. Negative single indexing is somewhat idiosyncratic
and not worth elaborating in this thread.

By the way, the code provided by Norman Jones will return not
necessarily the first blank cell, but the first blank cell after the
last nonblank cell in Column A. This will be ok only if there are no
blanks in the data.

More generally, if Cell A1 and A2 are not blank, you might consider

Set NextCell = Sheets("Sheet1").Range("A1").End(xlDown)(2)

Otherwise you will have to define the possible conditions and provide
for them in order to assign the first blank cell in the column; e.g.,

With Sheets("Sheet1")
If .Range("A1") = "" Then
Set NextCell = .Range("A1")
ElseIf .Range("A2") = "" Then
Set NextCell = .Range("A2")
Else
Set NextCell = .Range("A1").End(xlDown)(2)
End If
End With

Alan Beban


"Norman Jones" wrote in message
...

Hi Rui,

Try:

Dim NextCell As Range

Set NextCell = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2)

---
Regards,
Norman



"Rui" wrote in message
...

Hi,

I have two sheets of data I would like to merge. The problem is that

both

are variable. I would like to past the second sheet of data below the
first.
Can you please let me know the code to find the first blank cell in the
first
sheet?

Thanks
Rui

Find first blank cell
 

Alan: Thanks for indepth explanation.

Jim May

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Find first blank cell
 

I'm afraid in the discussion in my previous response I let myself get
sucked into another mistake in the Help. I referred to the Help's
reference Range.Item(2) as though "Range" was being used as the name of
a range; Range.Item(2) is in the form of a range assigned to a variable
named "Range", but this is actually not permitted. Range is a Reserved
keyword and not available as a variable name. In my discussion below
the use of, e.g., Range.Item(2) or Range(2) should have been to
something like MyRange.Item(2) or MyRange(2) where "MyRange" is the name
of a variable to which a range has been assigned.

Sorry for any confusion.

Alan Beban
Alan Beban wrote:
Jim May wrote:

Norman:
I see this all too often, that is the "(2)" at the very end of your
code.
What does it instruct VB to do?
TIA,

See Item Property (Range Object) in Microsoft Visual Basic Help. One
thing the Help does not clarify is that the "Item" can be omitted; i.e.,
Range("A1:A10").Item(1) can be expressed Range("A1:A10")(1). Another
slight error in the Help is that it indicates that if the name of the
relevant range is "Range", then Range.Item(2) will return the cell
immediately to the right of the upper left cell; that's true only if
Range has more than one column; if it does not, then you can refer to
the cell immediately to the right of the upper left cell with
Range.Item(1,2) [or, as mentioned above, Range(1,2)]; positive single
indexing can be used to refer only to cells within and directly below
the specified range. Negative single indexing is somewhat idiosyncratic
and not worth elaborating in this thread.

By the way, the code provided by Norman Jones will return not
necessarily the first blank cell, but the first blank cell after the
last nonblank cell in Column A. This will be ok only if there are no
blanks in the data.

More generally, if Cell A1 and A2 are not blank, you might consider

Set NextCell = Sheets("Sheet1").Range("A1").End(xlDown)(2)

Otherwise you will have to define the possible conditions and provide
for them in order to assign the first blank cell in the column; e.g.,

With Sheets("Sheet1")
If .Range("A1") = "" Then
Set NextCell = .Range("A1")
ElseIf .Range("A2") = "" Then
Set NextCell = .Range("A2")
Else
Set NextCell = .Range("A1").End(xlDown)(2)
End If
End With

Alan Beban


"Norman Jones" wrote in message
...

Hi Rui,

Try:

Dim NextCell As Range

Set NextCell = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2)

---
Regards,
Norman



"Rui" wrote in message
...

Hi,

I have two sheets of data I would like to merge. The problem is that


both

are variable. I would like to past the second sheet of data below the
first.
Can you please let me know the code to find the first blank cell in the
first
sheet?

Thanks
Rui