ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Select first empty row in a column (https://www.excelbanter.com/excel-discussion-misc-queries/219259-select-first-empty-row-column.html)

Tdungate
Select first empty row in a column
 
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
Select first empty row in a column
 
Eduardo, I have changed the column references numbers but now I get a
"Run-time error "1004""

"Eduardo" wrote:

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


"Tdungate" wrote:

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
Select first empty row in a column
 
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


"Tdungate" wrote:

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

Sheeloo[_3_]
Select first empty row in a column
 
Use
Dim lasRow as Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row + 1
End With

"Tdungate" wrote:

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

Dave Peterson
Select first empty row in a column
 
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

Tdungate wrote:

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

--

Dave Peterson

Roger Govier[_3_]
Select first empty row in a column
 
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
--
Regards
Roger Govier

"Tdungate" wrote in message
...
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
Select first empty row in a column
 

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" wrote:

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

"Eduardo" wrote:

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


"Tdungate" wrote:

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
Select first empty row in a column
 
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



"Dave Peterson" wrote:

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

Tdungate wrote:

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

--

Dave Peterson

Roger Govier[_3_]
Select first empty row in a column
 
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.

--
Regards
Roger Govier

"Tdungate" wrote in message
...
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



"Dave Peterson" wrote:

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

Tdungate wrote:

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

--

Dave Peterson

Tdungate
Select first empty row in a column
 
I managed to get the following to work for me. Thanks you all for your
assistance.

"Tdungate" wrote:

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



"Dave Peterson" wrote:

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

Tdungate wrote:

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

--

Dave Peterson


All times are GMT +1. The time now is 02:07 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com