How to skip an empty cell in a column ?

[kann]

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

Private Sub txt_BPName1_Exit(ByVal cancel As MSForms.ReturnBoolean)
Dim ws As Worksheet
Dim Lrow As Long
Dim c As Range, rng As Range

Set ws = ThisWorkbook.Sheets("Sheet1")
With ws

Lrow = .Range("A" & .Rows.Count).End(xlUp).row
Set rng = .Range("A2:A" & Lrow)
End With

For Each c In rng
If c.Value = txt_BPName1 Then
MsgBox "Cell " & c.Address & " Duplicate Found."
cancel = True

Exit Sub
End If
Next

MsgBox ("Base Product is not duplicate,Ok to Add")

Cells(Lrow + 1, 1).Value = txt_BPName1.Text

txt_BPName1.Text = ""
ActiveCell.Offset(1, 0).Select

End Sub

I tried using


If c.Value = "" Then

Lrow = Lrow + 1
End If

But it did not work. I would appreciate it, if anyone can take a look at this.

Regards,
kann

[Louwelyn]

Hi,

An idea:

Lrow = ws.UsedRange.Rows.Count + ws.UsedRange.Row - 1

[Bob Phillips]

Surely, this statement


Lrow = .Range("A" & .Rows.Count).End(xlUp).row

will get you the row of Kat4 not the empty row as you are working up from the bottom. I think your code already does it.

[Paul_Hossler]

or a little simpler since you want the range anyway




Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
Set rng = Range(.Range("A2"), .Cells(.Rows.Count,1).End(xlUp))
End With

[kann]

I tried all the codes above, but I am getting an entry into my empty cell instead of the one below it. One more thing , I would like to skip only an empty cell in that column, in case when I have values continuously like
kat1
kat2
kat3
I would like to see the next entry directly below kat3. I have attached the workbook(sample.xlsm), with just this example for reference. Please run the button BP_Name in sheet 1 and it should open an userform with a textbox for Base Product.

[Bob Phillips]

I am not sure what you think your problem is as that example didn't even have an empty row, but maybe you just need to replace


ACtivecell.Offset(1,0).Select

with


ws.Cells(Lrow + 1, "A").Select

[kann]

I would like my entries from the text box to excel sheet like this.
kat1
kat2
empty
kat3 ( this is ok. if it is skipping the empty cell above)
empty ( this is not ok. kat4 should be here, and not incremented below)
kat4.


ws.Cells(Lrow + 1, "A").Select is doing the same thing as
ACtivecell.Offset(1,0).Select

[Bob Phillips]

Not necessarily, the activecell may not be in the last row. Other than that, I have to admit I have no idea what you are trying to achieve, so I'll leave it to better men than me Gunga Din.

[kann]

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 ( this belongs to kat2)
empty ( this belongs to kat2)
empty( this belongs 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 )
kat4

[Paul_Hossler]

Maybe this?


15963






Option Explicit
Private Sub BP_Name_Exit(ByVal cancel As MSForms.ReturnBoolean)

Dim ws As Worksheet
Dim Lrow As Long
Dim c As Range, rng As Range

Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
Set rng = Range(.Range("A2"), .Cells(.Rows.Count, 1).End(xlUp))
For Each c In rng
If c.Value = BP_Name Then
MsgBox "Cell " & c.Address & " Duplicate Found."
cancel = True
Exit Sub
End If
Next

MsgBox ("Base Product is not duplicate,Ok to Add")

.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = BP_Name.Text
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Select

End With

End Sub

[Paul_Hossler]

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


How is Excel supposed to know that the cell below kat3 will have sub-product entered into it later so that entering kat4 will leave a blank cell after kat3?

[kann]

@ paul- Yes, you have a very valid point. I had an error in my logic. So, with empty cells in column A it will never be accurate. I have to get the base product name and store it in column A, every time I am adding more than one sub-product to a base product.

So, basically there will not be any empty cells in column A. Now, I have the problem fixed.

Thank you very everyone for your support.

Regards,
kann