Issue with VB code which duplicates data to below records.

[Nitefox]

I have the following code which duplicates the data in either column E or H (depending on which cell is active) to all the empty cells below as far as the last record (it checks for the last cell with data in the G column to find the last record). The formula works flawlessly as long as there are two or more records. If there is only one record then it is unable to find the last row, as it duplicates the data to every empty cell from Columns A to AD, and rows 1 to 944.

Please Login or Register  to view this content.

[patel45]

your code works on my test sheet, attach please a sample file with notes

[davesexcel]

Did not try it, but how about adding

Please Login or Register  to view this content.

[Nitefox]

Dave is this what your thinking:

Please Login or Register  to view this content.

I tried that but it didnt change anything.

I have attached my workbook. Go to the last row (326 on ReturnData sheet). If you select H326 and press the Duplicate Data Macro at the top it correctly copies the data to the two empty cells below. However if you select E327 and press Data Duplicate, being only one cell below to copy to, it copies it to a whole bunch of cells.

[patel45]

SpecialCells(xlCellTypeBlanks) does not work in a single cell, try this code

Please Login or Register  to view this content.

[Nitefox]

Thanks patel, that fixed it.

[Nitefox]

Patel I have just come across a bug with this code. If I select a cell on the last row of data and hit data duplicate it correctly duplicates that data to the row below, and if I select say a cell a few rows above the last row (and all the cells below it already contain data) then I correctly get an error message. However If I select the second to last row and hit Data Duplicate it copies the data to every blank cell on the workbook. How can I prevent this?
I have attached my workbook.

[patel45]

I'm sorry but I don't remember, tell me what sheet, what code,

[Nitefox]

Its on the ReturnData sheet, and this is the code:

Please Login or Register  to view this content.

The button Duplicate Data at the top runs the module.

[patel45]

Please Login or Register  to view this content.

[Nitefox]

Awesome, that fixed it. Thank you.