+ Reply to Thread
Results 1 to 5 of 5

How to select next empty cell in row for a specific range?

Hybrid View

  1. #1
    Registered User
    Join Date
    03-15-2016
    Location
    South Africa
    MS-Off Ver
    2007
    Posts
    5

    How to select next empty cell in row for a specific range?

    Hi

    I have a spreadsheet with with 8 columns and unlimited rows which needs to be filled in.
    How can I modify my current code so that when i press enter it goes to the next empty cell in row until the column 8 ("H") then to the next row?

    Currently this code only does column "A" and "B" - I need to "H"

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim erow As Long

    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Row

    If Cells(erow - 1, 1).Offset(0, 1) = "" Then

    Cells(erow - 1, 1).Offset(0, 1).Select

    Else: Cells(erow, 1).Select

    End If
    End Sub

  2. #2
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: How to select next empty cell in row for a specific range?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 Then
            Target.Offset(1, -7).Select
    Else
       Target.Offset(0, 1).Select
       
    End If
    
    End Sub

  3. #3
    Registered User
    Join Date
    03-15-2016
    Location
    South Africa
    MS-Off Ver
    2007
    Posts
    5

    Re: How to select next empty cell in row for a specific range?

    Thanks!

    However I also have an automatic sort. (To sort column C)
    It sorts only columns A to C then continues with the former row. - I need to sort the whole range
    Kindly see below my current code:

    Dim erow As Long
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Row
    Range("C:C").Select
    Range("A:H").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal



    If Cells(erow - 1, 1).Offset(0, 1) = "" Then
    'Cells(erow - 1, 1).Offset(0, 1).Select

    Else: Cells(erow, 1).Select
    End If

  4. #4
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: How to select next empty cell in row for a specific range?

    Whole range sort on what parameter ?? Basically this code is sorting the whole range on the basis of C column.

  5. #5
    Registered User
    Join Date
    03-15-2016
    Location
    South Africa
    MS-Off Ver
    2007
    Posts
    5

    Re: How to select next empty cell in row for a specific range?

    I need a code that will sort my spreadsheet based on column "C"
    But enable only after data is entered from "A" to "H" per row.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Select range until first empty cell in a column
    By Hadoushi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2015, 07:29 AM
  2. [SOLVED] Select non-empty cell range with VBA
    By mexykanu in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-09-2013, 10:24 AM
  3. [SOLVED] Select the last empty cell of the range specific
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2013, 09:48 AM
  4. cant select specific data and paste in next empty cell
    By comp23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2012, 08:01 AM
  5. Select Empty Cell Within a DropDown List Box's Range
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2010, 06:58 PM
  6. [SOLVED] Macro to find empty cell and select range to print selected.
    By vincentwongau@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2006, 04:10 AM
  7. [SOLVED] select first empty cell in a range
    By Shawn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-12-2005, 08:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1