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]

Can you post an example of your data

 

[daniel50096230]

I have no data of it....I have totally no idea on it.........

 

[daniel50096230]

Private Sub CommandButton2_Click()
For i = 1 To 10
ActiveCell.EntireRow.Validation.Delete
ActiveSheet.Unprotect
ActiveCell.EntireRow.Locked = False
ActiveCell.EntireRow.Insert
Cells(ActiveCell.Row + 1, 10).Resize(1, 20).Copy _
Destination:=Cells(ActiveCell.Row, 10)
Cells(ActiveCell.Row, 8).Clear ' Column H
Cells(ActiveCell.Row, 11).Clear ' Column K
Cells(ActiveCell.Row, 13).Clear ' Column M
Next i
End Sub

After I click the button,my Excel is totally hang,anyone can tell me what is the problem???

 

[sbeatton]

Can you post a copy of your table. Use http://www.mrexcel.com/forum/showthread.php?t=89356
or Excel Genie etc.

Then we can see what the code is trying to do with the data.

 

[daniel50096230]

http://i145.photobucket.com/albums/r229/daniel50096230/Picture1.jpg

You may view through this website since I am not really understand how to post up the image in here...From the image there,you can see my data is start from row 23 to 24022.....If I would like to add 10 rows simultaneously after row 32,then all the data start from row 32 will move down....But at the moment,I need to check if the move down all the data,will it exceed 24022...If exceed,messagebox will be prompt and cancel the insert.

 

[sbeatton]

Will the limit be a value in Row 24022 or is the limit the formatted row?

 

[daniel50096230]

I am just allowed the user to enter until row 24022 and cannot exceed it.

 

[sbeatton]

Try this

Option Explicit

Sub InsertRow()
'Check extent of data
Dim Lastrow As Long
    Lastrow = Cells.Find(What:="*", _
              After:=[A1], 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]

It occur the following error after I type the row number:

"The reference you type is not valid,or you did not provide a reference where one was required.To enter a refenrece using mouse,click in the edit box,and then click the cell in the sheet you want to refer to, or drag to the range you want to select."