Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Find current blank cell (97)

    I am new to EXCEL vba and am trying to locate the next blank cell in a column using vba code. Once the next the blank cell is located, I am copying data, from another work sheet in the same file, across many columns in that row (i.e., columns A thru K). For example if A10, is the next blank cell in column A, the macro needs to make this the active cell. My present routine identifies the last row with data (i.e., A9 in my simple example) and keeps copying different data over the same cells (i.e., over writing data and the table never grows beyond row 9). Sorry for this simple question, but after an hour of work, I have given up. THANKS for your patience with those just starting the learning, er, struggling process..

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

    Re: Find current blank cell (97)

    If you want to find the first blank cell below A1, you can use:

    Dim oCell As Range
    Set oCell = Range("A1").End(xlDown).Offset(1, 0)

    End(xlDown) is the code equivalent of hitting the End key followed by the Arrow Down key. Offset(1, 0) then returns the cell 1 row down (and 0 columnd to the right, i.e. in the same column.) In most cases, you don't need to select a cell or range to manipulate its contents. Methods like Copy and Paste work on ranges, and not selecting a range actually speeds up execution. But if you do need to select the cell, add

    oCell.Select

    If you want to find the blank cell below the last filled cell in column A, use

    Dim oCell As Range
    Set oCell = Range("A65535").End(xlUp).Offset(1,0)

    If for instance A1:A9 and A13:A18 are filled, this will return a reference to cell A19, while the first method would return a reference to cell A10.

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

    Re: Find current blank cell (97)

    Do you want the next blank cell in the column, or the cell after the last used cell in the column? Those will be different if there are any empty cells before the last used cell. If you want the cell after the last used cell in column A, then the following will give that:

    <pre> Worksheets("Sheet1").Range("A65536").End(xlUp).Off set(1,0)
    </pre>


    BTW, it is almost never necessary to select a cell in VBA to do something with that cell, and it is always much faster and better not to if you don't have to.
    Legare Coleman

Posting Permissions

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