UP/DOWN/INSERT/DELETE macros part of line

[chingilou]

hi! I would need if you still allow me your lights
I have a sheet with a table A14:F196 and other data at the top, bottom and right of the table
I'm looking to create 4 buttons in vba
1-to exchange the data of part of the selected row of column B to F with the one above.
2-exchange with the one below.
the exchange is limited from line 14 to line 196.
it means that if I am on line 14 the button up will not make the line go up and vice versa for line 196
and that the selection remains on the line to be moved up or down
3-the insertion makes empty the 5 columns of the sectioned line and that the data which was written there replace those of the line below and the data of that is in the line below and so on and the last are erased and replaced by the one above
4-a delete button which, unlike insertion, deletes the data of the 5 columns of the selected row and that are replaced by the one below and so on

I found on the net a macro to go up / down the whole line but I'm only interested in column "B" to "F"
and if possible to freeze the buttons on the sheets but I think it is impossible

[chingilou]

up

[Paul_Hossler]

Your requirements are confusing

Attach a sample workbook and not a screen shot to make it easier




1-to exchange the data of part of the selected row of column B to F with the one above.

2-exchange with the one below. the exchange is limited from line 14 to line 196.
it means that if I am on line 14 the button up will not make the line go up and vice versa for line 196
and that the selection remains on the line to be moved up or down

3-the insertion makes empty the 5 columns of the sectioned line and that the data which was written there replace those of the line below and the data of that is in the line below and so on and the last are erased and replaced by the one above

4-a delete button which, unlike insertion, deletes the data of the 5 columns of the selected row and that are replaced by the one below and so on


Using your sample workbook, explain 1 through 4 above by referring to SPECIFIC cells, including the selected cell.
The Up/Down/Right/Left arrow buttons do not seem to apply to Insert and Delete operations

For example for number 1


If in or above Row 9, do nothing

Select any cell in row 12.
Click button
Exchange B12:F12 and B11:F11



For example for number 2


If in or below Row 196, do nothing

Select any cell in row 12.
Click button
Exchange B12:F12 and B31:F13

[Paul_Hossler]

I took a guess for you at what seemed logical to me

Take a look and see if we can use this as a starting point

[chingilou]

thanks paul hossler it's almost perfect for me
there are 2 things left
the selection of lines does not follow the new order
example if I want to lower A1 to line 20 I must click on (two) 10 times and not select the line where A1 is (line10) and the macro (two) then reselect line 11 and the macro (two) and so on 10 times
what does (init) do and if you could put some explanatory notes on macros
Again, thank you very much

[Paul_Hossler]

thanks paul hossler it's almost perfect for me
there are 2 things left
the selection of lines does not follow the new order
example if I want to lower A1 to line 20 I must click on (two) 10 times and not select the line where A1 is (line10) and the macro (two) then reselect line 11 and the macro (two) and so on 10 times
what does (init) do and if you could put some explanatory notes on macros
Again, thank you very much





1-to exchange the data of part of the selected row of column B to F with the one above.

2-exchange with the one below.
the exchange is limited from line 14 to line 196.
it means that if I am on line 14 the button up will not make the line go up and vice versa for line 196
and that the selection remains on the line to be moved up or down

3-the insertion makes empty the 5 columns of the sectioned line and that the data which was written there replace those of the line below and the data of that is in the line below and so on and the last are erased and replaced by the one above

4-a delete button which, unlike insertion, deletes the data of the 5 columns of the selected row and that are replaced by the one below and so on



the selection of lines does not follow the new order
(#5) example if I want to lower A1 to line 20 I must click on (two) 10 times and not select the line where A1 is (line10) and the macro (two) then reselect line 11 and the macro (two) and so on 10 times

Don't know what "follow new order" means

5. This seems like a new requirement since the first 4 requirements focused on the active line, or the line above or below the active line, and not on two separated lines

It seems that you want to select a line (row 20), and then another line (row 40) and then exchange 20 <--> 40

I'll have to think on it


The sub Init() just has some common code it INIT-ialize each of the macros. The Sub One, Two, etc. are named for the requirement that I guessed at.
I'll make the names better after I think about your new requirement

[Paul_Hossler]

Ok, best I got

Select the first cell (A10)

Click on the button

Inputbox asks you to click the second row (A20)

Click [OK]

A10 and A20 are swapped


28228

[chingilou]

thanks very very much
sorry if I did not make myself understood I translate in English by google translate
I found the solution by adding at the bottom macro one & two

Rows(ActiveCell.Row - 1).Select or
Rows(ActiveCell.Row + 1).Select
the row swap good idea that can be useful one day

just a question that remains unanswered for me
can we freeze the buttons without put the buttons in a frozen area ?

THANKS

[Paul_Hossler]

thanks very very much
just a question that remains unanswered for me
can we freeze the buttons without put the buttons in a frozen area ?

THANKS

Only way I know

Design Mode
Right click shape
Select Size and Properties
Click Don't mode or size with cells



28231

[chingilou]

Only way I know

Design Mode
Right click shape
Select Size and Properties
Click Don't mode or size with cells

all the buttons are like this but it disappears with the lines if I go further down
(I moved the buttons outside the area of freeze cells)

[Paul_Hossler]

all the buttons are like this but it disappears with the lines if I go further down (I moved the buttons outside the area of freeze cells)


Don't move the buttons outside the Frozen Cell rows


The buttons stay with the cell that they are on. SO if you scroll the cell up or down the button goes with it


Why did you want to move the buttons out of the frozen area?

[chingilou]

because in my real file I have the frozen part full of buttons and formulas, no visible place
i noticed some other unwanted stuff about "insert" and "delete"
insertion must in addition to the insertion, delete the last line (196) while it adds after 196
delete must in addition go up line 196 to 195 and add an empty line to 196
in short my table A10: F196 must not grow nor shrink because I have data below the table
in A10: A196 I have formulas = IF (B14 <> ""; A13 + 1; "") if I apply delete it becomes = IF (#REF! <> ""; A13 + 1; "") how to fix that?
and thank you again in advance and sorry if i abuse my questions

[Paul_Hossler]

1. If you're pressed for space for buttons, I'd use the Customize Ribbon option (actually I'd use the Fluent CustomUI option, but Customize Ribbon is easier)

2. I had to loop to move B:F values up or down to not have your formulas adjust

However, the Col A are a little bit off since an Insert leaves Col B = "" and so Col A = "" which messes up the rest of the rows

What do you need / use Col A for anyway?

28233

[chingilou]

now it's perfect
sorry if i broke your head for a long time
for the buttons so to freeze them put them in the frozen area or ribbon ? no other alternative.



in any case thank you very much and problem solved

[Paul_Hossler]

No problem


I think that these buttons and any other macro buttons on the ribbon would be the best / only alternative since your 'frozen' area is full