CODEWITHSUNDEEP
vbaexcelrows
HattrickNZ
HattrickNZ

Reputation: 4643

VBA - Delete rows in another worksheet

Function DeleteRows()

Dim wb As Workbook
Set wb = Workbooks(ThisWorkbook.Name)

Debug.Print wb.Name

Dim wsName As String
Dim ws As Worksheet
wsName = "SheetX"
Set ws = wb.Worksheets(wsName)

Debug.Print ws.Name


With ws
        Debug.Print "ws.name = " & ws.Name
        Rows("3:31").Select

        Selection.Delete Shift:=xlUp

End With
End Function

I have this function, DeleteRows, that deletes the the rows 3:31 from the worksheet "SheetX".

This only works when "SheetX" is the sheet that is displayed on the screen.

I want it to work when another sheet, lets say "SheetY", in the workbook is displayed. How can I do this?

One way would be to: 1-activate "SheetY" 2-delete the rows 3-activate "SheetX"

But this would not be transparent to the user. Appreciate the help.

Upvotes: 2

Views: 16282

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

You should always try to avoid Select statements. They are almost always unnecessary. That function should be a Sub (because it doesn't have an output) and can be rewritten as a one-liner:

Sub DeleteRows()

    ThisWorkbook.Sheets("SheetX").Range("3:31").Delete xlUp

End Sub

Upvotes: 6

Related Questions