How can I set a range of cells using Cells with Range?

HI Stan,

the syntax for range using .cells is .range(.cells(row, column),.cells(row,column)) so to get D24:D41 using this syntax you need somthing like:

Sub Rnger1()
Dim DC As Range
Dim R0 As Long, Fixedn As Long
Dim ws As Worksheet: Set ws = Sheet1
R0 = 23
Fixedn = 18
With ws
Set DC = .Range(.Cells(R0 + 1, 4), .Cells(R0 + Fixedn, 4))
End With
DC.ClearContents
End Sub

Don't forget to wrap the code in your post, just highlight and click the code </> icon.

Regards

Justin

The with just allows a bit more flexibility and can save time if you add to the code later, the with can incorporate a number manipulations to the chosen object, range etc... without having to type that text each time similar to the use of declaring ws as a worksheet then setting it to a specific worksheet can allow you to modify a single line of code but adapts the requirement throughout the sub... it also allows you to move the code easily between forms and sheets often by changing only a couple of pieces of the code rather than looking through and finding every reference to the objects and ranges you need to change. As you note though, if you are only ever going to perform a single manipulation it probably simplifies the code to reference the object directly, if you intend to get more involved in VBA programming then making your code as adaptable as possible from the start is a good idea and you learn a great deal as you crash into the difficulties that arise in declaring and setting objects using variables on sheets, forms or within the code.

That is my understanding, there may be more to it if one of the programmers wants to jump in, glad my earlier response helped to fix your current issue.