+ Reply to Thread
Results 1 to 6 of 6

Remove Additional unwanted Rows

Hybrid View

  1. #1
    Registered User
    Join Date
    05-28-2013
    Location
    Aberdeen
    MS-Off Ver
    Excel 2010
    Posts
    35

    Remove Additional unwanted Rows

    Good Morning all.

    I have a problem solving what I am sure is easy once you know the syntax but still being relatively new to VBA I don't. I am trying remove additional rows after a selection has been coped from one worksheet to another I have used the following code to do the copying and pasting and this works fine, now what I am trying to do is once the below code has run and is completed any addition empty rows at the bottom of the copied data will be removed. I have tried adding the line
    Range(Selection, Selection.End(xlDown).Row + 1, 1).Select but get a compile error: Wrong number of arguments or invalid property assignment.

    Any help/advice would be appreciated.

    Thanks
    Sandy

    Sheets("January").Select
        ActiveSheet.Range("$A$5:$CN$105").AutoFilter Field:=92, Criteria1:="<>"
        Application.Goto Reference:="Jan"
        Selection.Copy
        Sheets("Annual Completed").Select
        Cells(Range("A1").End(xlDown).Row + 1, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveSheet.Range("$A$5:$CN$105").AutoFilter Field:=92
        Range("A5").Select
        Sheets("January").Select
        ActiveSheet.Range("$A$5:$CN$105").AutoFilter Field:=92
        Range("A6").Select

  2. #2
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Remove Additional unwanted Rows

    Hi, PLease share the file.. what are the additionla data after copy paste.. please help with an example in the file.. thanks
    Regards
    Parth

    I appreciate your feedback. Hit * if u Like.
    Rules - http://www.excelforum.com/forum-rule...rum-rules.html

  3. #3
    Registered User
    Join Date
    05-28-2013
    Location
    Aberdeen
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Remove Additional unwanted Rows

    File Attached as requested




    ITO Registration Register Rev 1.xlsm

  4. #4
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Remove Additional unwanted Rows

    You mean to say that The sheet January have data from Row A6 to A27
    Then it have Yello wcolored blank rows from A28 to A105

    Now this Blank rows i.e A28 to A105 should be deleted?

  5. #5
    Registered User
    Join Date
    05-28-2013
    Location
    Aberdeen
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Remove Additional unwanted Rows

    Hello Parth007, when I copy January to Annual Completed the code opens January Sheet, then using the filter on Column CN filter for non-blank cells, It then copies what is displayed over to the Annual Completed Sheet and pasted it in place. the problem is that every time I do that empty rows are also copied across and what I am trying to achieve that once copied to Annual Completed all blank rows below the last entry should be removed.

    I hope this makes sense

    Thanks
    Sandy

  6. #6
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Remove Additional unwanted Rows

    So in Annual Completed Sheet & January Sheet The column "CN" have data till 14th Row. & Ater that cells are blank irrespective of data present in other cells..

    So January Sheet should only have data till 14th row.. i.e. delete the blank rows after that?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Remove Unwanted Rows.
    By mattjchen2@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-03-2013, 07:20 AM
  2. [SOLVED] Remove unwanted rows in Excel 2010
    By kisboros in forum Excel General
    Replies: 11
    Last Post: 04-06-2013, 12:41 PM
  3. How to remove unwanted rows on multiple worksheets
    By adrianplunkett in forum Excel General
    Replies: 1
    Last Post: 09-22-2010, 10:01 AM
  4. Automatically remove unwanted rows
    By xlsior in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-04-2010, 01:03 AM
  5. Remove unwanted columns/rows
    By Ohucan2 in forum Excel General
    Replies: 1
    Last Post: 09-02-2009, 03:49 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1