Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2005
    Location
    London, Hampshire, United Kingdom
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding a vacant row (2002 10.2614.2625)

    Is there, please, any method of finding which is the first vacant row on a worksheet and pasting material into the first few cells in it?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding a vacant row (2002 10.2614.2625)

    Interactively, or in VBA code? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Is there a column that is guaranteed to be populated in every "used" row?

  3. #3
    Star Lounger
    Join Date
    Mar 2005
    Location
    London, Hampshire, United Kingdom
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a vacant row (2002 10.2614.2625)

    You can assume that the first nine columns of the top row and the first two columns and one of the next seven of every subsequent row down to the one I want to find will be populated.

    Ideally I wuld like to do it by a macro accessed by a button.

    Thanks

    Andrew.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a vacant row (2002 10.2614.2625)

    The following code will set oCell to the first cell in column A that is empty:

    <pre>Dim oCell As Range
    Set oCell = ActiveSheet.Range("A1").End(xlDown).Offset(1, 0)
    </pre>

    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding a vacant row (2002 10.2614.2625)

    If the first column will always be populated, the following will find the first empty cell in that column, hence the first cell in the first blank row:

    Worksheets("MySheet").Range("A65536").End(xlUp).Of fset(1, 0)

    or if the sheet is the active worksheet when you run the code, just use

    Range("A65536").End(xlUp).Offset(1, 0)

    Example usage:
    <code>
    Sub CopySomething()
    Worksheets("Source").Range("A1:F1").Copy _
    Destination:=Worksheets("Target").Range("A65536"). End(xlUp).Offset(1, 0)
    End Sub
    </code>
    This will copy A1:F1 from the Source worksheet into the first empty row in the Target worksheet.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a vacant row (2002 10.2614.2625)

    Actually, that will find the row after the last used row, not the first empty row.
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding a vacant row (2002 10.2614.2625)

    That is true, I interpreted the request that way. Andrew can choose the method he prefers.

  8. #8
    Star Lounger
    Join Date
    Mar 2005
    Location
    London, Hampshire, United Kingdom
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a vacant row (2002 10.2614.2625)

    Thank you both. Where there's a will there's a way, it just needs experts to find it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •