Select first empty row in a column

  • Thread starter Thread starter Tdungate
  • Start date Start date
T

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
 
Eduardo, I have changed the column references numbers but now I get a
"Run-time error "1004""
 
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
Click to expand...

The above will copy starting in column K to M
 
Use
Dim lasRow as Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row + 1
End With
 
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
 
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
 
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
 
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
 
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.
 
Back
Top