Insert Rows...Big Problem...

[daniel50096230]

I have a problem here...Firstly,I had fix my rows is from 1 to 100,and each ten rows consists as one row and I had use border to separate each ten rows.(Eg,row 1 to 10 is 1row).....So when I click a button and I choose to add 10rows after row 20,how can I do so?because when i choose to add 10rows after row 20,the existing row start from 21 will move down....But the problem is I had fix my rows to 100 only...How can I do so that the row 21 will move down and at the same time check the total of rows will not exceed 100?

 

[sbeatton]

AS it says, you need to select the row, not type a number.

 

[daniel50096230]

Ok...thanks...The code can run but don't know why it cause my Excel hang.....

 

[sbeatton]

What do you mean by hang? Does it give you an error message?

 

[daniel50096230]

There is no error message,but my program is not responding anymore......

 

[sbeatton]

It could still be running the macro as it searches all the cells to find the last empty one.

Try this alternative. It will search from the bottom (Row 24022) up rather than my original which was from Row 1 down.

Option Explicit

Sub InsertRow()
'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
End Sub

 

[daniel50096230]

Still having the same problem..I am not sure whether is my others coding cause this problem when I putting your code into there...

 

[sbeatton]

Can you post you code. I can then check if anything conflicts.

 

[daniel50096230]

This are all the code within the sheet...If you need the file,I may send to you too....

my email is daniel50096230@hotmail.com

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

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

 

[sbeatton]

Post you workbook on this site or a similar one, http://www.mediafire.com/ and paste the link. I'll pull it down and have a look.
By the way, I wouldn't post your email address on a forum as you'll end up with an inbox full of SPAM.

 

[daniel50096230]

I had post it on here:

http://www.sendspace.com/file/hfydi0

Anyone that willing to help,may download the file from here..Its virus free...