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

Try:

Formula:

Please Login or Register  to view this content.

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.

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).

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

Correction: I meant to say

Please Login or Register  to view this content.

. It should be correct in the file.

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

@ 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