none
excel 2013 delete method issue RRS feed

  • Question

  • Hi 

    I am using excel sheet 2013 and this is my vba code 

         

     Sheets("Test").Select
            ActiveSheet.Unprotect Password:="abc"
            Cells.Select
            Selection.Delete Shift:=xlUp

    I get an error 
    "delete method of range class failed vba"

    I also get an error 

    "method add of list objects failed" 

    but it works in excel sheet 2010 .

    I am  assuming there is a different method for 2013.

    Am i right if so what is the solution?

    thanks in advance


       


    nain1987

    Wednesday, August 13, 2014 12:47 PM

All replies

  • Hi Nain,

    As this issue is about Office Excel, I suggest you create a new thread on Office forum, more experts will assist you with Excel.

    Office forum:

    http://social.technet.microsoft.com/Forums/office/en-US/home?category=officeitpro

    Anyway, please check if they are useful:

    http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.delete.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

    http://www.exceltrick.com/how_to/delete-blank-rows-in-excel/

    Thanks,

    Wendy


    Wendy Li
    TechNet Community Support

    Thursday, August 14, 2014 2:53 AM
  • Hi 

     Const SERVER As String = "testsite "
            Const LISTNAME As String = "{}"
            Const VIEWNAME As String = "{}"

            strSPServer = "http://" & SERVER & "/_vti_bin"
            'Set objWksheet = ActiveSheet
            'Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
                'Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A3"))

                 Set objMyList = ActiveWorkbook.Worksheets(3).ListObjects.Add(SourceType:=xlSrcExternal, _
            Source:=Array(strSPServer, LISTNAME, VIEWNAME), LinkSource:=False, _
       Destination:=Range("A3"))
       MsgBox ("Added")

    I am using sharepoint 2010 and excel 2013 (earlier i was  using 2010 upgraded to 2013). to get the data  from sharepoint 2010 list to excel 2013 sheet using vb macro .this code works well in excel 2010.. but it gives an error in sharepoint 2013 that "method add of list objects failed" ?

    does sharepoint 2010  not support  excel 2013 methods? what is the solution?

    Thanks in advance 

                

    nain1987

    Monday, August 18, 2014 12:35 PM
  • If you want to delete all values you can create new Worksheet or use Clean method

    Cells.Clear

    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved


    • Edited by VBAToolsMVP Wednesday, October 1, 2014 3:03 PM
    Sunday, August 24, 2014 2:41 PM
  • Do you check solution?

    You can Add new worksheet and delete old one - same way.


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved


    • Edited by VBAToolsMVP Tuesday, September 23, 2014 9:24 AM
    Tuesday, September 23, 2014 9:24 AM