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

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

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.

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 .

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)

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

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.

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

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

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

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

Originally Posted by SKKS

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.

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.

Glad we could help, and thanks for the rep.