Select first empty row in a column

[Tdungate]

How do I select the first empty row in row "K". I want to use a form to copy
the following info to Row 2, Column "K".

Form Info:
Auto Year
Auto Make
Auto Model
Auto VIN

Worksheet Layout:
Col J Col K Col L Col M
Row 1 Year Make Model VIN
Row 2 2000 Toyota UBY xxxxxxxxx
Row 3 2000 Toyota UBY xxxxxxxxx

Please let me know.

I am currently using the following which ends up writing in Cols A-D, Row 2

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
nextrow = .Range("k65536").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow >= 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

 

[Tdungate]

Eduardo, I have changed the column references numbers but now I get a
"Run-time error "1004""

 

[Eduardo]

Hi,
The problem in you code is that you are telling it to copy in column A to D
change last part for this


..Cells(nextrow, 11).Value = MVYear

.Cells(nextrow,12).Value = MVMake
.Cells(nextrow, 13).Value = MVModel
.Cells(nextrow, 14).Value = MVVin

The above will copy starting in column K to M

 

[Sheeloo]

Use
Dim lasRow as Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row + 1
End With

 

[Dave Peterson]

This portion of your code writes the values to the worksheet:

With Worksheets("Lien Registration")
.Cells(nextrow, 1).Value = MVYear
.Cells(nextrow, 2).Value = MVMake
.Cells(nextrow, 3).Value = MVModel
.Cells(nextrow, 4).Value = MVVin
End With

Using .cells() allows you to specify the column as a number or a string.

So you can use:

With Worksheets("Lien Registration")
.Cells(nextrow, "K").Value = MVYear
.Cells(nextrow, "L").Value = MVMake
.Cells(nextrow, "M").Value = MVModel
.Cells(nextrow, "N").Value = MVVin
End With

or

With Worksheets("Lien Registration")
.Cells(nextrow, 11).Value = MVYear
.Cells(nextrow, 12).Value = MVMake
.Cells(nextrow, 13).Value = MVModel
.Cells(nextrow, 14).Value = MVVin
End With

 

[Roger Govier]

Hi

You are writing to the wrong column numbers
With Worksheets("Lien Registration")
.Cells(nextrow, 10).Value = MVYear
.Cells(nextrow, 11).Value = MVMake
.Cells(nextrow, 12).Value = MVModel
.Cells(nextrow, 13).Value = MVVin
End With

will write to columns J:M

 

[Eduardo]

Hi,
Did you delete the < in the code given before in the 2nd to the 4th line if
yes

Try one of this two options

1)
..Cells(nextrow, 1).Value = Me.MVYear.Value
..Cells(nextrow, 2).Value = Me.MVMake.Value
..Cells(nextrow, 3).Value = Me.MVModel.Value
..Cells(nextrow, 4).Value = Me.MVVin.Value

Or 2)

..Cells(irow, 1).Value = Me.MVYear.Value
..Cells(irow, 2).Value = Me.MVMake.Value
..Cells(irow, 3).Value = Me.MVModel.Value
..Cells(irow, 4).Value = Me.MVVin.Value

 

[Tdungate]

I am currently using a combination of your suggestion and Eduardos which is
giving me the same 1004 error result:

Private Sub CommandButton11_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Lien Registration")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Lien Registration")
'nextrow = .Range("k").End(xlUp).Row + 1
lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row + 1
End With
'always start with the even numbered row
If nextrow Mod 2 = 1 Then
nextrow = nextrow + 1
End If
'check to see if there's room
If nextrow >= 500 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Lien Registration")
..Cells(nextrow, "K").Value = Me.MVYear.Value
..Cells(nextrow, "L").Value = Me.MVMake.Value
..Cells(nextrow, "M").Value = Me.MVModel.Value
..Cells(nextrow, "N").Value = Me.MVVin.Value
End With
End Sub

 

[Roger Govier]

Hi you are now using lastrow to calculate row number (not Dimmed)
but still using nextrow (which will be 0) when trying the write the data.

 

[Tdungate]

I managed to get the following to work for me. Thanks you all for your
assistance.