1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Run-time error "1004": Cannot use that command on overlapping selections.

Discussion in 'Business Applications' started by sahancock, Nov 16, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. sahancock

    sahancock Thread Starter

    Joined:
    Nov 16, 2010
    Messages:
    2
    When I recorded this macro it worked, however, when I later attempt to use the macro on the same data I get the above error. Why?

    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete (This is highlighted when attempting to Debug).
    Columns("B:B").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
    Range("A:A,C:C,D:D,E:E,F:F,G:G").Select
    Range("G1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("G9").Select


    Thank you in advance for any assistance.
     
  2. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Your code works fine for me at the point where you state there's a problem, but for an apostrophie character to comment out your notes.

    Code:
     [COLOR=red][COLOR=Black]Selection.EntireRow.Delete[/COLOR] [COLOR=Lime]'[/COLOR][/COLOR][COLOR=blue][COLOR=Lime][put in apostrophie sysmbol to comment][/COLOR][COLOR=Black](This is highlighted when attempting to Debug).[/COLOR][/COLOR]
    
    It may be that you do not have any blank cells in column A, or that the whole of column A is blank.

    Can you explain more.

    Welcome to the forum btw.
     
  3. sahancock

    sahancock Thread Starter

    Joined:
    Nov 16, 2010
    Messages:
    2
    Thanks for the "Welcome".

    I figured out how to get it to work, although I don't quite understand why it was a problem.

    My A and B columns had some "wrapped text". After reformating, the macro worked. The thing I don't understand is why when I was recording the macro I didn't have any problems with the "wrapped text", but in running it I did. ???

    ps. Are you an Excel/VBA "geek"? I am just starting to use macro's and I love them. I wish I knew how to actually write the code. It's so much fun. :D
     
  4. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Wrapped text should'nt be a problem; but if you mean merged cells - then that could present you with a problem. Personally, I'd avoid merging cells in worksheets:(. To get round this, perhaps you could use...

    Code:
     
    Range("A1").EntireColumn.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete 
    
    But, if possible do not use merged cells to avoid unnecessary pain and suffering .:rolleyes:

    I must admit to gaining a bizarre joy by assisting others with their Excel woes here on this forum – I see it as being productive whist not being productive in my own work! I think you’ll find that this forum is crawling with VBA whizzes; so if you hang around, like I did, you’d soon pick it up.;)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/962725