+ Reply to Thread
Results 1 to 9 of 9

How to skip an empty cell in a column?

  1. #1
    Registered User
    Join Date
    04-04-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    29

    Post How to skip an empty cell in a column?

    My column A will have values like
    kat1
    kat2
    kat3
    empty
    kat4

    I would like the program to essentially skip the empty cell and use kat4 cell to set the last row. The code is as seen below
    Please Login or Register  to view this content.
    I tried using
    Please Login or Register  to view this content.
    But I keep running into " Method Range of Object _Worksheet failed error". Can any one please take a look at this.

    Thanks in advance,
    excelkann

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: How to skip an empty cell in a column?

    Try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-04-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    29

    Re: How to skip an empty cell in a column?

    code:
    Please Login or Register  to view this content.
    will make an entry into empty cell, I would like it to be in the next row. So, I tried using
    Please Login or Register  to view this content.
    . Although, this code works ok for skipping an empty cell, it does not work for the case where there is no empty cell for example. if I have

    kat1
    kat2
    empty
    kat3

    After kat3, I get an increment of two rows, instead of one.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: How to skip an empty cell in a column?

    I assume your last cell is actually not blank.
    It might contain somethin that appear blank ( like "" for example).

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

  5. #5
    Registered User
    Join Date
    04-04-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    29

    Post Re: How to skip an empty cell in a column?

    Attached file( sample.xlsm) contains my question. Please run BP_Name button in sheet 1 to open a user form for text box Base Product. When user enters the name here in the userform, it gets an entry into column A of sheet 1.
    Please Login or Register  to view this content.
    will skip an empty cell. But, when I have continuos values , userinput gets an entry into a second cell like seen below.

    kat1
    kat2
    empty
    kat3 ( here it is ok, because it is skipping an empty cell)
    Empty ( I would like kat 4 to be here, instead it is getting incremented to next row)
    kat4
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-04-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    29

    Re: How to skip an empty cell in a column?

    Correction: I meant to say
    Please Login or Register  to view this content.
    . It should be correct in the file.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: How to skip an empty cell in a column?

    What in words are you trying to do?

    Do you want to put the value entered in the textbox on the userform into the next empty cell in column A provided it's not a duplicate?
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  8. #8
    Registered User
    Join Date
    04-04-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    29

    Re: How to skip an empty cell in a column?

    Yes. The value entered in the textbox on the user form gets an entry into column A. But not always into the next empty cell . Empty cells below kat2 belong to its sub products and when user inputs kat3, it should not be stored in those empty cells. Instead, it should be stored into a cell after the empty cells.

    kat1
    kat2
    empty ( all of these belong to kat2)
    empty ( all of these belong to kat2)
    empty( all of these belong to kat2)
    kat3 ( if user input gets here without getting pasted into any of the empty cells above. it is ok & that's what I want)
    empty ( when user inputs kat4 after kat3, his input gets stored below instead of here with this code :
    Please Login or Register  to view this content.
    )
    kat4

  9. #9
    Registered User
    Join Date
    04-04-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    29

    Re: How to skip an empty cell in a column?

    @ Norie & @zbor- I realized that there is an error in my logic. With blank cells in column A , the process is not accurate. I have fixed it now and thanks very much for your code & time. It helped me.

    Thanks,
    excelkann

+ 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. Replies: 5
    Last Post: 10-27-2015, 01:54 PM
  2. Skip line if cell is empty
    By Krist2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-03-2015, 09:56 AM
  3. Skip empty cell in formula
    By Jeebos in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-01-2014, 03:00 PM
  4. Skip all empty cells (2 or more) using GET NEXT data command on a column
    By Newmord in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2014, 02:40 PM
  5. Skip to empty row or Creating a log
    By vikshrasss in forum Excel General
    Replies: 1
    Last Post: 01-25-2013, 11:24 AM
  6. Skip to empty row or Creating a log
    By vikshrasss in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 01-23-2013, 09:53 AM
  7. skip empty sheets
    By Rob in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2005, 10:06 AM

Tags for this Thread

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