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

 

[DataBlake]

next available line?

what constitutes as "available"? can you post an example table and expected result?

 

[Legacy 436357]

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.

 

[DataBlake]

If I delete any rows it will still post to the last line it was posted and leave the deleted rows blank.

so you're manually deleting the row?
if you right click > delete it will delete the entire row, thus not leaving a space.
or are you wanting the macro to paste the values in the blank row?

 

[Legacy 436357]

I am running this code from a command button which pastes the data. If I click it more than once by accident it has duplicates of the same data. So I manually delete the duplicates.

Then when I click the button again it will paste the data below the deleted rows that were duplicates. Thus leaving blank rows.

I want to avoid having these blank rows please.

 

[DataBlake]

I want to avoid having these blank rows please.

then right click > delete when you delete rows. it will delete the entire row rather than leave it blank

 

[Sam_D_Ben]

I guess assigning "xCount" to find the last empty row would work for him.

 

[Legacy 436357]

There is other information on the rows so it's not an option to delete the entire row.

Thank you

 

[DataBlake]

There is other information on the rows so it's not an option to delete the entire row.

so you're deleting cells and not the rows?
I'm assuming you delete JxCount, KxCount, and LxCount?
Then you want to re-do the macro for the empty cells after changes are made to H3 and H6?

it would be extremely helpful if you could post an example using any of the tools provided here
so that i can get a clear picture of the data instead of asking a bunch of questions. As well as what you want the expected results to look like.

after prying information from you it sounds like after deleting duplicates in columns J, K, and L (manually)
you make a change to H3 and H6.
Then run the macro again to replace what you just deleted?
If thats the case please let me know

 

[Legacy 436357]

Thanks I have asked too much already. I will figure something out.