How to change a to-delete-range without selecting it

Question

I tried everything which come to my mind to rewrite this code without the selection part.

Worksheets(3).Activate
ActiveSheet.ListObjects(ListName).Range.Select 'Listname is a string-variable
Range(ActiveCell.Offset(1), ActiveCell.Offset(-1)).Select
Selection.Delete Shift:=xlUp
Worksheets(2).Activate
MsgBox "Set-creation was canceled.", vbCritical

Try `Worksheets(3).ListObjects(ListName).Range.Offset(-1).Resize(2).Delete shift:=xlUp`. I do not work with tables much, so I am unsure of it working. — Scott Craner, Jan 25 '16 at 16:52
I recommend reading through [this SO thread](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — BruceWayne, Jan 25 '16 at 16:57
@Sir l33tname: my problem was that i tried to figure out how to write the code without .select. I tried to manage it with address resize and whatever. I mostly try to improve my vba-Skills by experimentiation but i did not hit the point this time. — Rohfleisch, Jan 26 '16 at 09:23
@Scott Craner: I'll give it a try. If it should work i'll tell you. — Rohfleisch, Jan 26 '16 at 09:25
@Scott Craner: Thx! It worked. Now i know the .resize method too. — Rohfleisch, Jan 26 '16 at 11:40

SierraOscar · Accepted Answer · 2019-01-18T11:15:17.817

Worksheets(3).Activate
ActiveSheet.ListObjects(ListName).Range.Select 'Listname is a string-variable
Range(ActiveCell.Offset(1), ActiveCell.Offset(-1)).Select
Selection.Delete Shift:=xlUp
Worksheets(2).Activate
MsgBox "Set-creation was canceled.", vbCritical

Can be re-written as:

With Sheets(3)
    .Range(.ListObjects(ListName).Range.Offset(1), .ListObjects(ListName).Range.Offset(-1)).Delete xlUp
End With    
Sheets(2).Activate    
MsgBox "Set-creation was canceled.", vbCritical

You don't need to .Select anything in excel-vba especially when dealing with a Range object - all it's properties and methods can be accessed directly.

Instead of:

Range("A1").Select
Selection.Value = "Test"

Consider:

Range("A1").Value = "Test"

This is better coding and in long procedures will run much faster. The other advantage is that you don't have to keep a track of what the Selection is when debugging or improving the code.

I already heard of the fact, that there is no activate or select necessary in VBA-Code ever. Im not as deep in the coding as i wish, so i try to improve it. I'll try your code later. Thx at all for the given informations and advices! — Rohfleisch, Jan 26 '16 at 09:28

How to change a to-delete-range without selecting it

1 Answers1