View Full Version : 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
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
04-18-2016, 10:50 PM
Hi,
An idea:
Lrow = ws.UsedRange.Rows.Count + ws.UsedRange.Row - 1
Bob Phillips
04-19-2016, 05:00 AM
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
04-19-2016, 05:53 AM
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
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
04-19-2016, 06:54 AM
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
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
04-19-2016, 07:26 AM
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.
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
04-19-2016, 07:47 AM
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
04-19-2016, 07:54 AM
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?
@ 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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.