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.

 

[DataBlake]

what you want to do is a loop through the columns to verify cells that are blank.

In order for me to help you can you verify:
you want to loop through all of your data for an entirely blank row. change the values of the completely blank row, and then copy that to sheet 2?
If not can you elaborate more on the overwriting part, etc??

 

[MARK858]

Have you tried the method below to see if it gives the row number that you want for the last row with data (I haven't put any offset in for the next blank row yet)?

MsgBox ActiveSheet.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

 

[stingwraith]

what you want to do is a loop through the columns to verify cells that are blank.

In order for me to help you can you verify:
you want to loop through all of your data for an entirely blank row. change the values of the completely blank row, and then copy that to sheet 2?
If not can you elaborate more on the overwriting part, etc??

Basically. I want to loop through and find the next entirely blank row on Sheet 2 and then copy the values from the row I've specified from Sheet 1. Essentially sheet 1 has only 5 usable rows(as far as the macro usage is concerned), and once the user runs the macro, it'll take the row they used it on and copy the values to sheet 2 for record-keeping.

 

[stingwraith]

Have you tried the method below to see if it gives the row number that you want for the last row with data (I haven't put any offset in for the next blank row yet)?

MsgBox ActiveSheet.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

I have not tried this method yet. If it can find the last row with data ANYWHERE on it, and an offset can select the next row below it, that's exactly what I'm looking for.

In simple terms I just want the next ACTUALLY empty row, not just the next row that doesn't have data in column A.

Their might not be any data in cell A, so it can't be used as a reference point.

 

[MARK858]

I have not tried this method yet.

Test it finds the last row that you want then post back your results

 

[stingwraith]

It appea

Test it finds the last row that you want then post back your results

It appears that just returns the row number that is user-selected on sheet 2

 

[stingwraith]

It appea


It appears that just returns the row number that is user-selected on sheet 2

Oops nvm, it works as intended.

 

[stingwraith]

Oops nvm, it works as intended.

How would I go about adding the offset?

 

[MARK858]

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