How to move down data?

[daniel50096230]

Hi,I would like to create a button,then I choose the row that I want the data to be move down..For example,when I choose the row 20,the data from row 21 to 30 will move down,and 31-40 will move down and so on....so that from row 21-30,there will be empty....

 

[Makrini]

Add this code to a button Macro..

Sub Macro1()
With Range(Selection.EntireRow, Selection.Offset(10, 0).EntireRow)
    .Insert Shift:=xlDown
End With
End Sub

 

[daniel50096230]

Hi,after I adding your code to my button,when I execute my button,my program is totally not responding.....Can you help me check my program?my program is just a very simple program and not much code inside there,just few statement....I can send you the file if you willing to help me...

 

[Makrini]

better that you post the code here - that way others can see and add suggestions also

 

[daniel50096230]

Option Explicit
Public r As Integer
Public s As Integer
Public d As Currency
Public z As Integer
Public y As Integer
Public sheetcount As Integer
Public i As Integer
Public e As Integer
Public b As Integer
Public c As Integer
Private Sub CommandButton2_Click()
'Check extent of data
'Dim Lastrow As Long
'    Lastrow = Cells.Find(What:="*", _
'              After:=[IV24022], SearchOrder:=xlByRows, _
'              SearchDirection:=xlPrevious).Row
'If Lastrow + 9 >= 24022 Then
'    MsgBox ("Rows will exceed 24022!")
'    Exit Sub
'Else
''Insert 10 rows
'Dim myRange As Range
'Dim AnsRange1 As Integer
'Dim AnsRange As Range
'Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8)
'AnsRange1 = myRange.Row
'Range(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown
'
'End If
With Range(Selection.EntireRow, Selection.Offset(10, 0).EntireRow)
    .Insert Shift:=xlDown
End With
End Sub
 
Private Sub Worksheet_Activate()
Worksheets("Input Form").Unprotect
'Call cell_locked
'Call Recalc
'Call add_validation
'Call add_borderline
'Call disable_command
'Call delete_validation
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=False
Application.CommandBars("Ply").Enabled = True

End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Call disable_command
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.Name = "Input Form" Then
Else
MsgBox "You are not allow to modify the name of this sheet!", vbCritical, "Not Allow"
ActiveSheet.Name = "Input Form"
End If
End Sub

This is my code...I already spend alots of time solving this problem....But still no solution...

 

[Makrini]

The exact code for command button that you have there works perfectly for me (Excel version 2003 - however I normally use 2000 at work and same code works there)

I would be guessing by your other code there that you have protection on your sheet which disallows the insert....

Either that or you have a LOT of formulas and your workbook stops responding whilst recalculating everything...

Anyone else have other ideas?

 

[Guanjin Peter]

i got a cheap way to do it:

copy and paste the whole cell.
paste at a2(if you want 2nd row onwards)
OR a3 for 3rd row...
OR a4 for 4th...row....etc.

let me know if this can work for you. i'll post the vb code

 

[daniel50096230]

Yes,I am also guessing this...So I make the worksheets unprotect...But it still having the same problems...This cause me alots of time,anyone have any idea??I really appreciate your help......

 

[daniel50096230]

Sure....can you post it up???So I can have a look on it or others can know about it...

 

[Makrini]

Check your pm and send the workbook - I'll have a look