Results 1 to 6 of 6

VBA - Finding Next Empty Row

This is a discussion on VBA - Finding Next Empty Row within the Excel Questions forums, part of the Question Forums category; Hi all, Here is a VBA query which probably has a simple solution... I have designed a user form which ...

  1. #1
    New Member
    Join Date
    Dec 2008
    Posts
    21

    Default VBA - Finding Next Empty Row

    Hi all,

    Here is a VBA query which probably has a simple solution...

    I have designed a user form which when you enter data into it and hit "ok" it sends the information to a worksheet named "data". For each new record entered into the form, the code I have used looks for the next empty row in the "data" sheet and adds the information. Here is the code:

    ActiveWorkbook.Sheets("Data").Select
    NextRow = _
    Application.WorksheetFunction.CountA(Range("A:A")) + 1
    Cells(NextRow, 1) = FamNameTB.Text
    Cells(NextRow, 2) = etc, etc, etc

    My problem is I need this to look for the next empty row, but start inputing the data in column B and leave column A blank. At the moment it sends the data to the first blank row starting in column A. I would have thought simply changing the range from Range("A:A") to ("B:B") would work but it doesn't. I have tried using the offset function but this also has not worked as my coding was probably incorrect. Any suggestions? Thanks.

  2. #2
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,490

    Default Re: VBA - Finding Next Empty Row

    Try

    Code:
    NextRow = Range("B" & Rows.Count).End(xlUp).Row + 1

  3. #3
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,181

    Default Re: VBA - Finding Next Empty Row

    Cells(NextRow, 1) = FamNameTB.Text

    the 1 is the column

    Just increment this number by 1

    obviously you'd count column b

    or you could use

    NextRow = range("B" & rows.count).end(xlup).row + 1
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  4. #4
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,115

    Default Re: VBA - Finding Next Empty Row

    Welcome to the boards!

    Try:

    NextRow = Sheets("Data").Range("B" & rows.count).End(xlUp).Row + 1
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: VBA - Finding Next Empty Row

    Well I've 2 suggestions to start with.

    1 Don't use Select, instead qualify the worksheet reference.

    2 Don't use CountA.

    If you want to find the next empty row in column A try something like this.
    Code:
    With Sheets("Data")
     
         NextRow = .Range("A" & Rows.Count).End(xlUp).Row+1
     
         .Range("B" &  NextRow) = TextBox1.Value
    End With
    Note this is psuedo code as the code you posted and your explanation are hard to follow.
    If posting code please use code tags.

  6. #6
    New Member
    Join Date
    Dec 2008
    Posts
    21

    Default Re: VBA - Finding Next Empty Row

    Thanks for the quick replies. All sorted now. Much appreciated.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com