Insert entirerow improvement needed [SOLVED]

[Kieranz]

Hi

I hope someone can help me with improving the following code:

Explaination would be most helpful.

Rgds

Kieranz


Sub InsertRow()

Dim sClrC As String, iActvC As Integer


sClrC = "D1:J1,L1,N1:O1,U1"

iActvC = Range("lrnInsertCurr").Column


Range("InsertRow").Select

ActiveCell.Offset(-1, 0).EntireRow.Select

Selection.Copy

Selection.insert shift:=xlDown

Range("InsertRow").Offset(-1, 1).Select

Application.CutCopyMode = False

ActiveCell.Offset(0, -iActvC).Range(sClrC).ClearContents

Range("InsertRow").Offset(-1, 1).Select

End Sub

 

[Debraj]

Hi Kieranz,


Do you want to know.. what this code is doing...


or do we need to know.. what you want to be done.. by this code..


any help with detail of NameRange for "lrnInsertCurr" & "InsertRow" will be much appreciated..


and regarding imporvement of code.. I can only do a lot of INDENTATION..


Regards,

Deb

 

[Kieranz]

Hi Deb

I have name range "InsertRow" which is attached to a button. This will insert a row above the name range and clear the specified cell contents. My mistake the "lrnInsertCurr should have read "insertRow". Its working the way it is. I just thought there may be a better way or improvement to the code. There is too much select and selection and repeatation.

Many thks.

Rgds

Kieranz

[pre]

Sub InsertRow()
Dim sClrC As String, iActvC As Integer
sClrC = "D1:J1,L1,N1:O1,U1"

iActvC = Range("InsertRow").Column
Range("InsertRow").Select
ActiveCell.Offset(-1, 0).EntireRow.Select
Selection.Copy
Selection.insert shift:=xlDown

Range("InsertRow").Offset(-1, 1).Select
Application.CutCopyMode = False
ActiveCell.Offset(0, -iActvC).Range(sClrC).ClearContents
Range("InsertRow").Offset(-1, 1).Select
End Sub

[/pre]

 

[Debraj]

Hi Kieranz!


I am again confused...


* NamedRange : InsertRow

* Sub Name : InsertRow


Is the above code is working!!!


Can you please post your file..


Refer:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Deb

 

[Kieranz]

Hi Deb

My mistake the sub should read InsertLine.

The code works. What i am asking for is improving the coding there is too much duplication as in:


Range("InsertRow").Select

ActiveCell.Offset(-1, 0).EntireRow.Select

Selection.Copy

Selection.insert shift:=xlDown


Can i not avoid select selection but how!


Rgds

KN

 

[Debraj]

Hi Kieranz!


In VBA.. you dont need to select..

You can simple write..

[pre]

Sub InsertLine()
Dim sClrC As String, iActvC As Integer

sClrC = "D1:J1,L1,N1:O1,U1"
With Range("InsertRow")
iActvC = .Column - 1
.Offset(-1, 0).EntireRow.Copy
.Offset(-1, 0).EntireRow.Insert shift:=xlDown
.Offset(-1, -iActvC).Range(sClrC).ClearContents
End With
End Sub

[/pre]

Regards,

Deb

 

[Kieranz]

Many thks, Deb. It makes sense

Rgds

KN

 

[Debraj]

Glad that you liked it..