Next Available Line

[Legacy 436357]

Hi,

Can this code be edited so it puts the data on the next available line? I sometimes need to delete some data.

Thanks

Private Sub CommandButton1_Click()
Static xCount As Integer
    Application.EnableEvents = False
    
        Range("J3").Offset(xCount, 0).Value = Range("H6").Value
        Range("K3").Offset(xCount, 0).Value = Range("H3").Value
        Range("L3").Offset(xCount, 0).Value = Format(Now(), "HH:MM:SS")
        xCount = xCount + 1
    
    Application.EnableEvents = True
End Sub

 

[Joe4]

Thanks for replying. The first time I click the button it posts data on J3, K3, L3. Then on J4, K4, and L4 and so on. If I delete any rows it will still post to the last line it was posted and leave the deleted rows blank. I want it to post to the next blank row so there aren't gaps.

I think the issue is that you are not updating your "xCount" variable when you are deleting rows.

Instead of trying to store that variable, why not just find the end/start dynamically each time, like this?

Private Sub CommandButton1_Click()

    Dim lastRow As Long
    
    Application.EnableEvents = False
    
'   Find last row with data in column J
    lastRow = Cells(Rows.Count, "J").End(xlUp).Row
    
    Range("J" & lastRow + 1).Value = Range("H6").Value
    Range("K" & lastRow + 1).Value = Range("H3").Value
    Range("L" & lastRow + 1).Value = Format(Now(), "HH:MM:SS")
    
    Application.EnableEvents = True

End Sub

 

[MARK858]

@xjohnson, assuming that you have data in J2,K2 and L2 (post back if you don't) does the below do what you want?

Private Sub CommandButton1_Click()

    Application.EnableEvents = False
    
        Cells(Rows.Count, "J").End(xlUp)(2).Value = Range("H6").Value
        Cells(Rows.Count, "K").End(xlUp)(2).Value = Range("H3").Value
        Cells(Rows.Count, "L").End(xlUp)(2).Value = Format(Now(), "HH:MM:SS")

    Application.EnableEvents = True
End Sub

Edit: the above is almost the same as what Joe4 has just posted (it just works out the next row separately for each column as I am not confident that you are deleting all 3 cells everytime).

 

[Fluff]

If you only ever delete one row at atime, how about

Private Sub CommandButton1_Click()
Static xCount As Integer
    Application.EnableEvents = False
      If Range("J3").Offset(xCount - 1).Value = "" Then xCount = xCount - 1
        Range("J3").Offset(xCount, 0).Value = Range("H6").Value
        Range("K3").Offset(xCount, 0).Value = Range("H3").Value
        Range("L3").Offset(xCount, 0).Value = Format(Now(), "HH:MM:SS")
        xCount = xCount + 1
    
    Application.EnableEvents = True
End Sub

 

[Legacy 436357]

Thanks Joe4 but nothing happens when clicking the button with your code.

 

[Joe4]

Something should definitely be happening. Are you sure that you placed the code in the right place, and it is attached to your command button?
Easy way to check is to add a MsgBox.

Private Sub CommandButton1_Click()

    Dim lastRow As Long
    
    Application.EnableEvents = False
    
'   Find last row with data in column J
    lastRow = Cells(Rows.Count, "J").End(xlUp).Row
    MsgBox "Code is running and last row is " & lastRow
    
    Range("J" & lastRow + 1).Value = Range("H6").Value
    Range("K" & lastRow + 1).Value = Range("H3").Value
    Range("L" & lastRow + 1).Value = Format(Now(), "HH:MM:SS")
    
    Application.EnableEvents = True

End Sub

Try running it again. If you do not get a MsgBox pop-up, then your code and command button are not linked together.
If it does pop-up, confirm that it is returning the correct value for the last row.


EDIT: Do you possibly have other data in column J underneath the last row that you want to update?

 

[Legacy 436357]

Thanks Fluff but there may more than one row deleted. I might go back after a day or so of invoice entries (this records the total, invoice number, and time).

 

[MARK858]

Thanks Joe4 but nothing happens when clicking the button with your code.

If nothing happens with the code Joe4 has posted have you reset your macro? and also run the code below and try again.

Sub Resetit()
    Application.EnableEvents = True
End Sub

 

[Legacy 436357]

The message helped there were entries made at row 607 so I deleted all and ran it again. It still doesn't remove the blanks though.

Excel Workbook
	J	K	L
1	Total Invoice History
2	Invoice Totals	Invoice Number	Time Stamp
3
4
5
6
7
8	$210.00	104	01/00/1900 10:45:19
9
10	$210.00	104	01/00/1900 10:45:33
11
12	$210.00	104	01/00/1900 10:46:03
Lookup Invoice

Private Sub CommandButton1_Click()

    Dim lastRow As Long
    
    Application.EnableEvents = False
    
'   Find last row with data in column J
    lastRow = Cells(Rows.Count, "J").End(xlUp).Row
    MsgBox "Code is running and last row is " & lastRow
    
    Range("J" & lastRow + 1).Value = Range("H6").Value
    Range("K" & lastRow + 1).Value = Range("H3").Value
    Range("L" & lastRow + 1).Value = Format(Now(), "HH:MM:SS")
    
    Application.EnableEvents = True

End Sub

 

[Joe4]

The code I wrote was in regards to your original question, how to add the data to the next available row (so it dynamically finds the last row with data, and then puts the new information on the row just below that).
It does not delete any data.

 

[Fluff]

How about

Private Sub CommandButton1_Click()
   Dim NxtRw As Long
   Application.EnableEvents = False
   If Range("J3").Value = "" Then
      NxtRw = 3
   Else
      NxtRw = Range("J2").End(xlDown).Offset(1).Row
   End If
   Range("J" & NxtRw).Value = Range("H6").Value
   Range("K" & NxtRw).Value = Range("H3").Value
   Range("L" & NxtRw).Value = Format(Now(), "HH:MM:SS")

   Application.EnableEvents = True
End Sub