VBA Range().delete wont work

ChristmasCarol

New Member
Joined
Apr 17, 2018
Messages
7
Hello,

I'm stuck at a Piece of Code where I try to delete all rows from row 2 to row (variable).

My Code Looks like that:


Code:
dim lng_rdel as Long
dim wb_paste as workbook

lng_rdel = wb_paste.Worksheets("csv").UsedRange.SpecialCells(xlCellTypeLastCell).Row

wb_paste.Worksheets("csv").Range(Rows(2), Rows(lng_rdel)).EntireRow.Delete

I looked it up and it seems to me that the Syntax is correct but I always get an error 1004 at the last Code line.

What do I miss?:confused:
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Welcome to the Board!

The syntax tax is not quite correct. Since lng_rdel is a Long number and not a Range, you cannot apply Rows to it (it makes no sense to apply Rows to a number).

Try this:
Code:
wb_paste.Worksheets("csv").Rows("2:" & lng_rdel).Delete
 
Last edited:
Upvote 0
Have you "Set" your Workbook Variable, as below ???
Code:
Dim wb_paste As Workbook
Set wb_paste = Workbooks("MyBook.xlsm")
 
Upvote 0
Another option would be
Code:
With wb_paste.Worksheets("csv")
   .Range(.Rows(2), .Rows(lng_rdel)).EntireRow.Delete
End With
You need to qualify the Rows inside your range. In your code the Rows are looking at the activeworksheet, not the csv worksheet
 
Last edited:
Upvote 0
Hi Joe4 and thank you!
It works perfectly.

Such Things are always my Problem. I thought, I'd give the "number" of the row I want to delete, so Long would be fine... :oops:
And as all examples only where like .. rows(4:5) without a variable, I didn't see the error.

thanks again for your quick help!
 
Upvote 0
Try this.
Code:
dim lng_rdel as Long
dim wb_paste as workbook

lng_rdel = wb_paste.Worksheets("csv").UsedRange.SpecialCells(xlCellTypeLastCell).Row

With wb_paste.Worksheets("csv")
    .Range(.Rows(2), .Rows(lng_rdel)).EntireRow.Delete
End With
 
Upvote 0
Such Things are always my Problem. I thought, I'd give the "number" of the row I want to delete, so Long would be fine...
It is! The way you want about finding that row number is fine. You just need to realize that the number is just that, a number.
The issue was in just how you were trying to reference it after the fact.

A good tip/trick in these cases is to use the Macro Recorder, and record yourself manually performing an example of what you are trying to do (delete a bunch of rows).
Then stop the Recorder, and inspect your code. This is what you are trying to build. So that can help give you a "road map" of what you are trying to build in VBA.
 
Upvote 0
Hello Norie and Fluff,

thanks for your answers.
I dont' understand, why I need that.

I thought that with
Code:
wb_paste.Worksheets("csv").Rows(...
wb_paste would be used for Rows.

Am I wrong?
 
Upvote 0
In the original code, below, neither instance of Rows has a worksheet (or workbook) reference so will refer to the active sheet in the active workbook.
Code:
wb_paste.Worksheets("csv").Range(Rows(2), Rows(lng_rdel)).EntireRow.Delete
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top