PDA

View Full Version : [SOLVED] Move up cells less than row value



bull699
02-19-2016, 12:11 PM
I have a data table. When the user clicks 'delete' the applicable row is deleted. I want the data from the remaining rows underneath to move up one cell.


rs = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row

I set rs as the row of the application caller (i.e. the rows that's being deleted) so I want every cell with a value higher that rs to move up one.

I guess it would take a while to cycle through every cell in the sheet? The table is set between columns B-P. At the moment the only column that has an entry in every row is B, so is there a way to work off that and to get the loop to stop once it hits a cell in column B without an entry?

Thanks in advance

p45cal
02-20-2016, 03:09 AM
Show us the current lines which 'delete'.
If it ends .delete then usually excel closes the gap either by moving cells up or to the left depending on the Shift argument which can be xlShiftToLeft or xlToLeft (the same) or xlShiftUp or xlUp (also the same).
If it ends .entirerow.delete it should already close the gap.
If you're 'deleting' with .clear or .clearcontents then no movement of cells occurs.

Which is it?

p45cal
02-20-2016, 03:22 AM
I see you spread yourself around a bit on a regular basis.
This question has been cross posted without links:
http://stackoverflow.com/questions/35513146/excel-vba-move-cell-values-up-from-application-caller
http://www.excelforum.com/excel-programming-vba-macros/1127637-move-up-cells-less-than-variable-row-value.html

bull699, for your information, you should always provide links to your cross posts.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184

SamT
02-20-2016, 06:30 AM
Have you received an answer at any other location yet?


I set rs as the row of the application caller (i.e. the rows that's being deleted) so I want every cell with a value higher that rs to move up one.

I assume you mean that you want all rows below the deleted Row to move up since rs is a Row number. IF you are talking about Cell Values, we would need a bit more information.

bull699
02-23-2016, 01:55 PM
Thanks for the help, and thanks for the advice on cross posting, I'll definitely keep that in mind in the future.


Show us the current lines which 'delete'.
If it ends .delete then usually excel closes the gap either by moving cells up or to the left depending on the Shift argument which can be xlShiftToLeft or xlToLeft (the same) or xlShiftUp or xlUp (also the same).
If it ends .entirerow.delete it should already close the gap.
If you're 'deleting' with .clear or .clearcontents then no movement of cells occurs.

Which is it?

Sorted. I was trying to use .clearcontents and then looking for a way to move the remaining cells up. Ended up using .delete xlShiftUp