Finding next TRULY empty row - VBA

[stingwraith]

Hello, I've recently been attempting to get the next completely empty row in order to add data to it. Normally this would be pretty straight-forward, however I'm running into an issue where if the last row contains values that AREN'T in the cell on column A, then it will overwrite the last line. i.e.: if there's something on the last line in the B column but NOT in the A column, it will be found to be an empty line and be overwritten.

The goal of this is to execute the macro on values on 'Sheet1', then have those values removed from 'Sheet1' and added to a new line on 'Sheet2', essentially keeping a record.

Here's what I've tried so far:

Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row 1
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.Offset(1).Select 'This line moves the selected cell down by one.
Range("A1").End(xlDown).Offset(1, 0).Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Range(Selection, Cells(Rows.Count, Selection.Column).End(xlDown)).Select

Any help would be greatly appreciated.

 

[stingwraith]

If you want it to paste in the next blank row in Sheet2 then

Sheets("Sheet2").Range("A" & Sheets("Sheet2").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row).Offset(1).PasteSpecial

OMG it works!!! Thanks so much for your help!!

 

[MARK858]

Happy it helped and welcome to the forum.