+ Reply to Thread
Results 1 to 4 of 4

Macro not deleting rows

Hybrid View

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Macro not deleting rows

    Hi All,

    I'm trying to use a macro to transpose blocks of data, then delete the data that was transposed. I've created a macro, but it isn't deleting the rows -- the final step of the macro.

    Below is an example of what it looks like.

    A1 A1 A2 A3 A4 A5 A6 A7
    A2
    A3
    A4
    A5
    A6
    A7

    B1
    B2
    B3
    B4
    B5
    B6
    B7


    I would like it to delete the column of As once they've been transposed, which would shift the Bs one row underneath the newly transposed As. The macro, however, is just leaving the column of A's intact, or it's pasting in a specific cell.

    here's the macro:
    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+M
    '
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("B79").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Range("A79").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    End Sub

    I'm assuming the error has to do with the exact cell references. How do I prevent this?

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro not deleting rows

    Please use code tags with your code as per forum's rule.
    Difficult to see the layout of the data. Please attach an excel sample book.

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro not deleting rows

    Please used code tags when posting.

    Maybe:

    Sub acsherman()
    With Range(Selection, Selection.End(xlDown))
        .Copy
        Range("B79").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        .Delete Shift:=xlUp
    End With
    End Sub

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro not deleting rows

    Sub Macro2()
    '
    Set Rng1 = Selection
    Range(Selection, Selection.End(xlDown)).Select
    Set Rng2 = Selection
    Rng2.Copy
    Rng1.Offset(, 1).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Rng2.Delete Shift:=xlUp
    End Sub
    If solved remember to mark Thread as solved

+ 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] Macro help with combining data across 2 rows (and deleting non-required rows)
    By InnesMcc in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 05-20-2013, 05:21 AM
  2. How to make my macro delete rows? It currently hides the rows instead of deleting.
    By Majkataxmk in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-20-2010, 01:14 PM
  3. Deleting rows with macro
    By Elise.B in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-19-2009, 06:10 AM
  4. Deleting rows Macro
    By noxxero in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-27-2007, 12:57 PM
  5. Deleting rows with macro
    By PhilScratchingmyhead in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-29-2006, 01:00 PM

Tags for this Thread

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