Results 1 to 8 of 8

VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

This is a discussion on VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again within the Excel Questions forums, part of the Question Forums category; Hi Guys, I found part of this code in another topic in these fantastic forums, but I just need a ...

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Location
    Jarrow
    Posts
    70

    Default VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

    Hi Guys,

    I found part of this code in another topic in these fantastic forums, but I just need a little hand to personalise it a little bit more for what I would like it to do.


    Code:
     
    Sub Insert()
        Selection.EntireRow.Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("C2").Select
    End Sub
    This works just fine to insert a new row wherever the selected cell is on the worksheet and then to select cell C2 to finish off.

    But what I would like to do is once the new row is inserted, I would like to select whatever the original cell was in the first place.

    For example, if cell C10 is selceted, click the button to run this code and for then it to select cell C10 to finish off.

    For example, if cell C17 is selceted, click the button to run this code and for then it to select cell C17 to finish off.

    So this line of the code "Range("C2").Select" needs to somehow select the original cell that was selected in the first place... any ideas?

    Thanks
    Chris Williamson
    mr.chris.williamson [at] hotmail.co.uk

  2. #2
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,237

    Default Re: VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

    You don't need to select

    Code:
    Sub Insert()
    Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Oct 2010
    Location
    Jarrow
    Posts
    70

    Default Re: VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

    Bravo,

    Many thank you's.
    It makes sence now... don't select the entire row in the first place then I wont have this problem haha.

    Regards
    Chris Williamson
    mr.chris.williamson [at] hotmail.co.uk

  4. #4
    Board Regular
    Join Date
    Oct 2010
    Location
    Jarrow
    Posts
    70

    Default Re: VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

    It was working just fine at first, but now I get a run-time error message..

    Run-time error '-2147417848 (80010108)

    Method 'AutoFit' of object 'Range' failed
    Any ideas?
    Chris Williamson
    mr.chris.williamson [at] hotmail.co.uk

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,644

    Default Re: VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

    Chris

    Is there more code?

    I don't see AutoFit in what you've posted so far.
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Oct 2010
    Location
    Jarrow
    Posts
    70

    Default Re: VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

    No it is just this

    Code:
     
    Sub Insert()
    Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End Sub
    I am however inserting this into a tabel (alt T), but I don't think that will matter? Will it...
    Chris Williamson
    mr.chris.williamson [at] hotmail.co.uk

  7. #7
    Board Regular
    Join Date
    Oct 2010
    Location
    Jarrow
    Posts
    70

    Default Re: VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

    I think I have pin pointed the problem now...

    There is other code in the worksheet, but I was only getting this error message when inserting a new row.

    My Songlist Star Rank
    This topic I was asking to change 1, 2, 3, 4 or 5 to that number of stars.
    Chris Williamson
    mr.chris.williamson [at] hotmail.co.uk

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,644

    Default Re: VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

    Chris

    I had a feeling there was some change event code involved somewhere.

    Now that you've found the root of the problem have you fixed it, or do you still need help?

    I think that if you either removed the AutoFit or changed it to just autofit the column(s) you are interested in then it might work.

    You might also want to add a further check on the Target parameter.

    One other thing that might help would be to turn off events.

    You can do that like this.
    Code:
    Application.EnableEvents = False
     
    ' code for change event
     
    Application.EnableEvents = True
    Last edited by Norie; Nov 14th, 2010 at 11:22 PM.
    If posting code please use code tags.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com