+ Reply to Thread
Results 1 to 27 of 27

Run Time Error 1004: Cannot Use Command on overlapping selections

  1. #1
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Run Time Error 1004: Cannot Use Command on overlapping selections

    Hello vba Experts,
    The purpose of my vba module is to find rows with blank cells and then delete the row with the blank cell. Code pasted in attached text.

    When run, vba returns: Run Time Error 1004: Cannot Use Command on overlapping selections. The error occurs on the line calling for deleting the specified row. I have identified that the error is coming from the ?Item? column (Column F, see attached spreadsheet). I tried copying and pasting the format from the ?Name? column (Column G), to no avail. I know that the ?Item? column is the culprit because when I delete that column the code runs fine. I have real data with thousands of lines so I really need to get the code working or source of error fixed. Thank you.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Pick a single column that will always be blank when that is a row you want deleted
    e.g. keeping subTotals then Const REF_COLUMN As String = "J" , not keeping subtotals then Const REF_COLUMN As String = "I"


    Please Login or Register  to view this content.
    Please consider clicking "Add Reputation" if this code has helped . thx
    Last edited by nimrod1313; 08-22-2022 at 01:07 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Hello. A way:

    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Careful with .SpecialCells(xlLastCell)).Rows

    Hello Beyond Excel

    Be very careful with .SpecialCells(xlLastCell)).
    This does not necessarily find the last cell in sheet with a value , it finds last cell that ever had a value.
    For example lets say you have a sheet with values to row 2500 .
    You then clearContents on all rows below 100.
    The SpecialCells(xlLastCell)) will still return 2500 as lastCell
    SpecialCells(xlLastCell)) is looking for cells that are null/nullstring , we usually define lastRow as cells that are empty.


    DEMO
    Please Login or Register  to view this content.
    ScrShot17.gif
    Last edited by nimrod1313; 08-22-2022 at 02:23 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Hi nimrod1313.
    Your previous comment is valid in general terms but nothing indicates that this is the case.

    Nevertheless, be very careful with selections as narrow as a single column:

    > Set myRange = .Range(.Cells(1, REF_COLUMN), .Cells(lastRow, REF_COLUMN))

    That way row 2 is deleted, for example, while the query wants to delete entire empty rows.
    That is why you have to analyze what happens in all the columns (from A to J).

  6. #6
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    beyondExcel,

    I am fairly new to vba, can you explain what your suggested code does ( your first reply)? Can you explain why I am getting an error? Thank you.

  7. #7
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    be very careful with selections as narrow as a single column
    I agree that one has to be carefull when selecting the column but in this case , like most others , there will be a column that will always have a value that is predictable before hand (i.e. Id number , customer name etc).
    And this can be predicted. However using "SpecialCells(xlCellTypeLastCell)" you cannot predict how many cells have ever been used in the sheet. Hence alot more rows then needed may be involved.

    One way to find last row in any column, when necessary , would be this .

    Please Login or Register  to view this content.
    Last edited by nimrod1313; 08-22-2022 at 02:34 PM.

  8. #8
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    nimrod1313 and beyond Excel:

    Is my method of selection causing the error? Have either of you tested on my workbook?

  9. #9
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Is my method of selection causing the error?
    I was able to replicate your error with the range selection you were specifiing.
    Instead of spending alot of time trying to make it work I saw that you had certian
    columns that would always have a value so I chose one of those to use .
    Try my code and if not giving you results you want let me know and I will help fix it so that it does .
    Last edited by nimrod1313; 08-22-2022 at 02:48 PM.

  10. #10
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    nimrod,

    I'll try your code to select the active area. It is the case that I will need to use the code on other data. Stay tuned!

  11. #11
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    I'll try your code to select the active area. It is the case that I will need to use the code on other data. Stay tuned!
    If you do have a certain column that will always have a value my approach is fairly typical approach.
    For example , in your case, wouldn't a valid row always have a Name (column E) or a Qty ( column G) .. Amount (colunn J) ?
    Then just pick one of these columns as your reference column (ie. REF_COLUMN ) .
    Every time "REF_COLUMN" is blank the entireRow will be deleted.

    Another nice feature of this method is that it deletes ALL unwanted rows at once. So very little excel processing overhead (ie. wasted time)
    Last edited by nimrod1313; 08-22-2022 at 02:58 PM.

  12. #12
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Here is the modified code - I get the following eror Run Time Error 1004: Application or Object-Defined Error



    Sub NimRod_DelRows()

    Dim myWorksheet As Worksheet
    Dim myRange As Range
    'Dim lastColumn As Long
    'Dim lastRow As Long

    Application.ScreenUpdating = True

    Set myWorksheet = Worksheets("Sheet1")

    Dim LastUsedCell As Range
    Dim lastRow, lastColumn

    With ActiveSheet.Cells
    Set LastUsedCell = .Find(What:="*", LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    lastRow = LastUsedCell.Row
    lastColumn = LastUsedCell.Column
    End With

    Set myRange = myWorksheet.Range(Cells(2, 2), Cells(lastRow, lastColumn))
    myRange.Activate

    'On Error Resume Next
    myRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    'On Error GoTo 0



    End Sub
    Last edited by SKKS; 08-22-2022 at 03:04 PM.

  13. #13
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    It is the case that I will need to use the code on other data. Stay tuned!
    If you have many places(i.e other data sheets) then you can have a generic procedure that is called ... see example

    Please Login or Register  to view this content.
    Last edited by nimrod1313; 08-22-2022 at 03:13 PM.

  14. #14
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Here is the modified code - I get the following eror Run Time Error 1004: Application or Object-Defined Error
    Use my code as is .
    There is no need to find last row anymore .
    You just choose a sheet and reference column and your done .
    note reference sheet differs e.g ThisWorkbook.Sheets("Sheet1"), activeSheet, activeWorkBook.sheets("SheetABC") ... etc

    Try this exact code , as is, on "Sheet1" like you uploaded to us.

    all you configure is in bold Call deleteRowsByBlankColumn(ThisWorkbook.Sheets("Sheet1"), "J")

    Please Login or Register  to view this content.
    SO ... In your code , if you have many places you want to delete rows , the only thing you would include is " Call deleteRowsByBlankColumn(ThisWorkbook.Sheets("Sheet1"), "J")" .... and of course include the procedure it call in one of your modules
    Last edited by nimrod1313; 08-22-2022 at 03:27 PM.

  15. #15
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    nimrod,

    At this point I am confused. Why the "application or object type error" ?

    What is the purpose of the last set of code you posted?

    I apologize for my lack of understanding.

  16. #16
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    At this point I am confused. Why the "application or object type error"
    To get unconfused just run the last example I posted.
    Get that running first .. then adapt it to use in your code .
    Eventually , The code you have now will be cleared out and replaced with a single line " Call deleteRowsByBlankColumn(ThisWorkbook.Sheets("Sheet1"), "J")"

    BUT FOR NOW ...
    Dont look at you code
    Open a new module and copy my code in
    Put you sample "Sheet1" into same workbook
    run the macro I've provided.

    Dont stress ... be happy .. you got some great code and ideas
    Last edited by nimrod1313; 08-22-2022 at 03:33 PM.

  17. #17
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    t this point I am confused. Why the "application or object type error" ?

    What is the purpose of the last set of code you posted?

    I apologize for my lack of understanding.
    I've uploaded a demo for you click/upload this .... DemoDeleteRows.xlsm
    ... run the macro DemoDelete and see how sheet1 has rows removed

    Please consider clicking "Add Reputation" if this code has helped . thx
    Last edited by nimrod1313; 08-22-2022 at 03:46 PM.

  18. #18
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Quote Originally Posted by nimrod1313 View Post
    I've uploaded a demo for you click/upload this .... Attachment 792884
    ... run the macro DemoDelete and see how sheet1 has rows removed

    Please consider clicking "Add Reputation" if this code has helped . thx

    I downloaded your demo, and of course it worked and I now understand why you only have to select one row!


    However, I do not understand, and would like to learn why my method did not work. Also what is the purpose of the Public Sub "DemoDelete" and why the "J" string must passed to another Sub named "deleteRowsByBlankColumn" ?

  19. #19
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Also what is the purpose of the Public Sub "DemoDelete" and why the "J" string must passed to another Sub named "deleteRowsByBlankColumn" ?
    The "Public Sub "DemoDelete" is there to demonstrate to you that any procedure/macro in your code doesn't need to do the work of deleting.
    The calling procedure , in this example "DemoDelete" just call the other procedure/macro with the sheet Object and the column letter to look for blanks in.

    So you could , in any of your long procedures, just add a single line to get any sheet delete blank rows with just this .. Call deleteRowsByBlankColumn(ThisWorkbook.Sheets("Sheet1"), "J")

  20. #20
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Also what is the purpose of the Public Sub "DemoDelete" and why the "J" string must passed to another Sub named "deleteRowsByBlankColumn" ?

    Your posted code ...

    Please Login or Register  to view this content.
    IS replaced by this ...

    Please Login or Register  to view this content.
    Note: The prodedure "deleteRowsByBlankColumn" must be located in the workbooks modules as well

    Please consider clicking "Add Reputation" if this code has helped . thx
    Last edited by nimrod1313; 08-22-2022 at 05:03 PM.

  21. #21
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Quote Originally Posted by SKKS View Post
    However, I do not understand, and would like to learn why my method did not work.
    Because you have blank cells in column F on some rows where there is data in column G but not H and I. That means your specialcells call creates two areas on the same row, and then entirerow means you have the same row included twice. That is what breaks the code. You can work around that with something like:

    Please Login or Register  to view this content.
    Rory

  22. #22
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Please Login or Register  to view this content.
    [/QUOTE]

    Rory,

    Thank you your suggestion did work! Your explanation of why my original code creates two areas makes sense.

    I kind of understand the concept of the Intersect formula and I did some homework and tried to figure out what exactly the Intersection "is" as applied to my data (which cells are intersecting and why?) If you could provide a detailed explanation of why it works on my data that would be very helpful for my future learning and coding!

  23. #23
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Quote Originally Posted by nimrod1313 View Post
    [B]



    Please consider clicking "Add Reputation" if this code has helped . thx
    I added to your reputation, THANK YOU

  24. #24
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Intersect returns a reference to where two ranges overlap - eg the intersect of A1:B6 and B2:C5 is the cells that are in both ranges, namely B2:B5.

    In this case we are looking for where your original range and all the used rows of the worksheet overlap. Intersect is naturally very good at resolving overlapping areas in either of its range arguments, so it removes the issue that was causing your original code to fail. For example, if the specialcells(xlcelltypeblanks) returned references to F2 and H2:I2, applying entirerow to that would give you two references to row 2. Intersect effectively flattens the two references together to give just one reference to row 2.
    Last edited by rorya; 08-23-2022 at 12:20 PM.

  25. #25
    Valued Forum Contributor
    Join Date
    05-03-2022
    Location
    Halifax,Canada
    MS-Off Ver
    365
    Posts
    326

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    I added to your reputation, THANK YOU
    Thanks so much ... please don't hesitate to ask further questions

  26. #26
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Thank you nimrod and Rory. I'm marking this one as solved.

  27. #27
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Run Time Error 1004: Cannot Use Command on overlapping selections

    Glad we could help, and thanks for the rep.

+ 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. Named ranges & error 1004 "cannot use that command on overlapping sections....
    By locolobo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-25-2022, 07:21 AM
  2. Replies: 10
    Last Post: 11-10-2021, 09:49 AM
  3. Error: Cannot use that command on overlapping selections. Please help
    By ufondu88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2018, 12:35 PM
  4. Run-time error 1004: this action won't work on multiple selections
    By aprildu in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-08-2018, 12:50 PM
  5. [SOLVED] Runtime 1004 Error - Cannot Use that Command On Overlapping Sections
    By stevoDE in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-13-2017, 11:18 AM
  6. [SOLVED] Run-time error '1004' when executing Exit Sub command line
    By Henk Stander in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-29-2014, 02:50 AM
  7. Run Time Error 1004 - You cannot use this command on a protected sheet
    By frogboy in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-11-2012, 07:59 AM

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