macro shift the row of the selected cell up or down

[mark692]

Book1
	A	B	C	D	E
1	Date	Name	Price	Formula 1	Formula 2
2	25-Jan	Jam	2000	no	no
3	26-Jan	Richard	2500	no	no
4	27-Jan	James	3000	no	no
5
6
7
8
9
10
11
12
13
Sheet1

hi guys i have a table example is the above table, i want to make a button that will shifts rows from A to C up or down depending on the selected cell. is that possible? thanks

 

[James006]

Hi,

You can have a double-click event to achieve what your objective ...

You will need to be more specific regarding the destination row ... :wink.

 

[mark692]

Hi,

You can have a double-click event to achieve what your objective ...

You will need to be more specific regarding the destination row ... :wink.

i want it to shift above sir when i press the button, so that will be the destination

 

[James006]

Hi again,

Below is your Event macro to be tested ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Double-Click to Move Up
If Target.Column <> 1 Then Exit Sub
Dim i As Long
i = Target.Row
Application.EnableEvents = False
Application.ScreenUpdating = False
    Rows(i - 1 & ":" & i - 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A" & i + 1 & ":C" & i + 1).Copy Destination:=Range("A" & i - 1)
    Rows(i + 1 & ":" & i + 1).Delete Shift:=xlUp
Application.ScreenUpdating = True
Application.EnableEvents = True
Cancel = True
End Sub

HTH

 

[mark692]

Hi again,

Below is your Event macro to be tested ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Double-Click to Move Up
If Target.Column <> 1 Then Exit Sub
Dim i As Long
i = Target.Row
Application.EnableEvents = False
Application.ScreenUpdating = False
    Rows(i - 1 & ":" & i - 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A" & i + 1 & ":C" & i + 1).Copy Destination:=Range("A" & i - 1)
    Rows(i + 1 & ":" & i + 1).Delete Shift:=xlUp
Application.ScreenUpdating = True
Application.EnableEvents = True
Cancel = True
End Sub

HTH

i pasted your code in the module but when i assign it on a button it dosnt appear

BTW i saw this code

Sub FloatUp()

Dim arr As Variant
Dim r1 As Range
Dim r2 As Range

Set r1 = Selection
If r1.Row = 1 Then
MsgBox "Top reached"
Else
Set r2 = r1.Offset(-1).Resize(1)
arr = r2
r2.Resize(r1.Rows.Count) = r1.Value
r2.Resize(r1.Rows.Count).Select
r1.Resize(1).Offset(r1.Rows.Count - 1) = arr
End If


End Sub

and it works but the problem is it only shift the selected cell, i dont wat to select all the row manually, i want it to automaticaly select the range of row where the selected cell is,

example if i want to select B1 to C1 and D1 to F1, ill just select a cell on row 1, then press the button and it will shift B1:C1 and D1:F1 to top,

 

[My Aswer Is This]

You said:

example if i want to select B1 to C1 and D1 to F1, ill just select a cell on row 1, then press the button and it will shift B1:C1 and D1:F1 to top,

So if you select any cell on Row 1 and the press button you want B1:F1 shift to Top

What is Top?

Do you mean Row(1)
​

 

[mark692]

You said:

example if i want to select B1 to C1 and D1 to F1, ill just select a cell on row 1, then press the button and it will shift B1:C1 and D1:F1 to top,

So if you select any cell on Row 1 and the press button you want B1:F1 shift to Top

What is Top?

Do you mean Row(1)
​

sorry sir its "E1" not "D1"

if i select any cell on a row "B to C" and "E to F" will go up, this is it sir.

 

[My Aswer Is This]

I see now you have posted this question on this form twice Today in different threads.

 

[mark692]

I see now you have posted this question on this form twice Today in different threads.

yes sir, is that wrong? i really need to finish this project

 

[James006]

i pasted your code in the module but when i assign it on a button it dosnt appear

For your information ...an EVENT macro is a macro which does NOT need a button ... since it is triggered by an Event...

In this case, the event is whenever your double-click in any cell located in Column A ...

Hope this clarifies ...