VBA Range().delete wont work

[ChristmasCarol]

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:

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?

 

[ChristmasCarol]

Would this be correct?

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

This seems to include the Information two times: first in front of range, then again in front of rows.

 

[Norie]

Yes that would work but, to me anyway, repeating the wb_paste.Worksheets("csv") part is a bit much - that's why I used With ... End With in the code I posted.

 

[ChristmasCarol]

Thank you Norie, now I understand better!

However I came across a second part of Code that doesn't work properly. And maybe the missing reference ist also the Problem here.
Would you mind take a look again?

The Code is supposed lookup a value in column 73 (from row 2 to the last filled row) and compare it to a variable (arr_zv (1 to y)). If there's a match, the whole row should be pasted into another tab. There are different Tabs (arr_tab(1 to y)) that are filled depending on the value in column 73

Dim lng_lastrow_paste As Long
y = 5
Dim arr_zv(1 To y) As String
Dim arr_tab(1 To y) As String

lng_lastrow_paste = ...

For i = 2 To lng_lastrow_paste + 1                                                                
        For j = 1 To y
            If ActiveSheet.Cells(i, 73).Value2 = arr_zv(j) Then                                              
            ActiveSheet.Rows(i).Copy
            Set r = Worksheets(arr_tab(j)).UsedRange
            lng_empty = r.Rows(r.Rows.Count).Row + 1
            ActiveWorkbook.Sheets(arr_tab(j)).Rows(lng_empty).PasteSpecial
            Exit For
            End If
        Next j
    Next i

ActiveSheet has about 600 rows of data (with Fitting values in column 73) and only 2 are pasted.

 

[ChristmasCarol]

It's supposed to be:

y = 5
Redim arr_zv(1 To y) As String
Redim arr_tab(1 To y) As String