MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do you code to find the next empty row...

Posted by Steve on April 19, 2001 11:30 PM

I run a macro twice a month to scan each workdays sheets (one sheet per workday) that would make up the payperiod. The scan copies information from selected cells on the workday sheets to a payperiod sheet based on certain criteria. So far this works great but I now need to change this to do the scan each day which means the payperiod sheet will accumulate data on a daily basis instead of all at once. I am trying to figure out how to write the code to locate the next empty row on the payperiod sheet. The row would be considered empty if row starting at cells(1,12) have nothing in them. Ie: the data starts in cell 1 and row 12 of the payperiod sheet. All rows before that are headers and other static info. Example of my sheetnames:

sheets named 1,2,3...31 are the workday sheets
sheet named pperiod1 is the info copied from sheets 1-15
sheet named pperiod2 is the info copied from sheets 16-31

Posted by Dominic on April 20, 2001 1:29 AM


I do not understand exactly what you mean, your text is just too long. But try this:

Range("A1").Select ' or any Cell
Selection.End(xlDown).Select 'Goes down to the last Cell with content (also possible with UP)
ActiveCell.Offset(1, 0).Range("A1").Select 'Goes one row deeper