VBA Delete Cells (Delete Shift:=xlUp)

[CarolynS]

Hi

I've written some code to determine whether I need to delete or insert cells in to spreadsheet (depending on whether I have more or less data than the last time the code was run).

If the sheet I'm deleting the cells from is active then the code runs fine. However, the sheet won't normally be active and when it's not, I get runtime error 1004 (Method 'Range' of object '_worksheet' failed. I'm not sure why this is happening as I'm referencing the workbook and sheet in the code (code runs from another workbook).

The specific line of code I'm having issues with is:

WSCE.Range(Cells(7, 2), Cells(RowNum1 + 6, 10)).Delete shift:=xlUp

this is part of a larger piece if code:

    Set WSCE = WBUtil.Worksheets("Failure Rates")

    RowNum1 = WSID.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
    RowNum2 = WSCE.Range("Table1").Rows.Count - 1
    
    FinalRow = RowNum1 - RowNum2
    
    'add or remove rows as needed
    
    If FinalRow < 0 Then
    
        RowNum1 = FinalRow * -1
    
        WSCE.Range(Cells(7, 2), Cells(RowNum1 + 6, 10)).Delete shift:=xlUp
    
    ElseIf FinalRow > 0 Then
    
        WSCE.Range(Cells(7, 2), Cells(fnalrow + 6, 10)).Insert shift:=xlDown
    
    Else
    
    End If

There is a lot more code before this but far too much to post and I don't think it is relevant.

I haven't tested the adding rows scenario yet but I'm guessing I may have the same issue?

Any help greatly appreciated

 

[CarolynS]

I think now I need to use:

WSCE.Range(WSCE.Cells(7, 2), WSCE.Cells(RowNum1 + 6, 10)).Delete shift:=xlUp

?