Select a varaible range to delete the data . the range may vary every time the macro runs

Question

   Range("A1").Select
   Selection.End(xlDown).Select
   ActiveCell.Offset(0, 3).Select

   Dim Q As String
   
   Q = ActiveCell.Address
   
   Range(" Q:D10438").Select
   Selection.ClearContents

The Variable Q is taking the value of the active cell, but i am unable to include it in range

If you're trying to find the last row in column A, [see this approach](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Dec 04 '20 at 20:53

score 0 · Answer 1 · answered Dec 04 '20 at 21:49

0

The variable cannot be included in the quotes. Also you should avoid using Select. A simpler version would be:

   Dim Q As String
   Q = Range("A1").End(xlDown).Offset(0, 3).Address
   Range(Q & ":D10438").ClearContents

answered Dec 04 '20 at 21:49

Darrell H

1,876
1
9
14

score 0 · Answer 2 · answered Dec 05 '20 at 11:03

Range Object

How to avoid using Select

When defining a range from two cells, the Range object 'allows' us to use cell range objects or cell range addresses or a mix of them:

Dim rng as range
Set rng = Range("A1","D4")
Set rng = Range(Range("A1"), Range("D4"))
' Mix
Set rng = Range("A1", Range("D4"))
Set rng = Range(Range("A1"), "D4")

to get the range:

$A$1:$D$4

We can apply the second 'mix solution' to the question:

' First cell range
Range("A1").End(xlDown).Offset(0, 3)
' Second cell address
"D10438"

to get the result:

Range(Range("A1").End(xlDown).Offset(0, 3), "D10438").ClearContents

Select a varaible range to delete the data . the range may vary every time the macro runs

2 Answers2

Range Object