Excel VBA - Move cell values up from application caller

Question

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

MatthewD · Accepted Answer · 2016-02-19 19:36:44Z

3

You can do that in the delete command.

Range("Your Range”).Delete Shift:=xlUp

That code is for a sheet row but you can adapt it to your table.

Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
ws.ListObjects("Table3").Range.Rows(4).Delete xlShiftUp

That will delete the fourth row of the table. Yours would probably be

ws.ListObjects("Table3").Range.Rows(rs).Delete xlShiftUp

Here is some good info on working with tables. http://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

edited Feb 19, 2016 at 19:36

answered Feb 19, 2016 at 19:14

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

W.White Over a year ago

Worked a charm. Was trying to use .clearcontent. Thanks

MatthewD Over a year ago

Please click the little check mark under the up down arrows to let the community know that it has been answered.

Collectives™ on Stack Overflow

Excel VBA - Move cell values up from application caller

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related