How to deleting selected range of cells when range is dependent on a variable?

Question

I want to delete the cells in the range G123:P10000, but the value of 123 in G123 keeps on changing, so how can I provide the variable while providing range.

Below can be used a one time, but I want to use it multiple times based on the value in the variable named 'count' which changes for every run so that it deletes from that range only

Range("G123:P10000").Select
Selection.ClearContents

I tried something like below, but its not working

count = ActiveSheet.Range("A1").End(xlDown).Row
Range("G$count:P10000").Select
Selection.ClearContents

1) `Range("G" & count & ":P10000").Select` 2) [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) 3) [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, Apr 23 '14 at 19:32

John Alexiou · Answer 1 · 2015-08-04T18:17:03.753

What you need is the .Offset() and .Resize() modifications for range.

...
count = CountRows(ActiveSheet.Range("A1"))
ActiveSheet.Range("G1").Offset(count,0).Resize(10000-count,1).ClearContents

with

Public Function CountRows(ByVal r As Range) As Long
    If IsEmpty(r) Then
        CountRows = 0
    ElseIf IsEmpty(r.Offset(1, 0)) Then
        CountRows = 1
    Else
        CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.Count
    End If
End Function

How to deleting selected range of cells when range is dependent on a variable?

1 Answers1