Post Data on the Next Available Blank Cell

[joeyjj]

Hello all,

I am having a difficult time trying to figure out how to configure my code to work the way I want it it. Below is my code:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Input_Data")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a Name
If Trim(Me.SRCName.Value) = "" Then
  Me.SRCName.SetFocus
  MsgBox "Please enter the width"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.SRCName.Value
ws.Cells(iRow, 13).Value = Me.SRCName.Value
ws.Cells(iRow, 14).Value = Me.F63.Value
ws.Cells(iRow, 15).Value = Me.F125.Value
ws.Cells(iRow, 16).Value = Me.F250.Value
ws.Cells(iRow, 17).Value = Me.F500.Value
ws.Cells(iRow, 18).Value = Me.F1000.Value
ws.Cells(iRow, 19).Value = Me.F2000.Value
ws.Cells(iRow, 20).Value = Me.F4000.Value
ws.Cells(iRow, 21).Value = Me.F8000.Value
ws.Cells(iRow, 22).Value = 1

'clear the data
Me.SRCName.Value = ""
Me.F63.Value = ""
Me.F125.Value = ""
Me.F250.Value = ""
Me.F500.Value = ""
Me.F1000.Value = ""
Me.F2000.Value = ""
Me.F4000.Value = ""
Me.F8000.Value = ""
Me.SRCName.SetFocus

End Sub

The purpose of this code is to open a dialogue box for a user to input data. My issue is that I would like to have the data copied to the next blank row starting from the top down. The reason for this is that I have a macro linked to a button that would take my current selected line and add a line so the user can bring add things in the middle of the data sheet if they missed something. Any help is much appreciated.

 

[VoG]

Perhaps like this

iRow = ws.Range("A1").End(xlDown).Row + 1

 

[joeyjj]

Never thought about using a range instead of a cell. THanks sooooo much.

 

[joeyjj]

Perhaps like this

iRow = ws.Range("A1").End(xlDown).Row + 1

I tried using this but my iRow value becomes too large around 100k and proceeds with an error?

 

[VoG]

Maybe like this

If ws.Range("A2").Value <> "" Then
    iRow = ws.Range("A1").End(xlDown).Row + 1
Else
    iRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
End If

 

[joeyjj]

Maybe like this

If ws.Range("A2").Value <> "" Then
    iRow = ws.Range("A1").End(xlDown).Row + 1
Else
    iRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
End If

Thank you so much!! It is working now Your the best!