Thread: fixing a block of cells

Hi there,

I have a spredsheet with a block of information (B100 to E104), when i delete a line of information anywhere above this block, the starting row of this block is now minus 1 (ie 99) and keeps reducing everytime i delete a line however i need to keep this block on the same row number, any ideas of how i can achieve this??

Thanks in advance for any help

Probably the easiest way is to delete the contents of the rows above the block you want to preserve, rather than deleting the entire row itself, which always moves up the contents of cells below. Depending on what information you want to preserve, you might also try edit > delete > shift cells left.

The problem with that is that i have a user form that inputs information into the next open row and rows 6-100 contain formulae that are applied to the information. So if a row is cleared it just leaves a blank row somewhere in the data base. if the row is deleted it doesnt affect the next input and there are no gaps but then the cells i want to stay at line 100 move upwards

In that case I think you will have to insert a row at row 99 every time you delete a row further up the spreadsheet. You can do this manually each time or use a simple macro such as this:

Sub row_add()
Range("A99").EntireRow.Insert
End Sub