Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the first blank row in a cell | Excel Discussion (Misc queries) | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
find the first and last non blank cell in a row | Excel Discussion (Misc queries) | |||
find next blank cell | Excel Programming | |||
Find and blank cell then Do this..... | Excel Programming |