How to delete values of a range in Excel VBA

Question

Hello, I have a range in excel named " ValuesRange" located at Cell "B5". I have four values: 3,4,5,6 located at cells B6,B7,B8,B9 respectively. Given that B10 is always blank. how can delete the four values one by one? Here is the code that I used:

Dim startRange as Range

Set startRange= Range("ValuesRange").offset.(1,0) while not IsEmpty(startRange) startRange.value= " " startRange=startRange(1,0) Wend

This code does not work. what it does is that it deletes the first values (3) and then replace it with number 4 and keeps doing that in infinite loop. please help me to fix this code. Thanks a lot!

Answer 1

Here's one way:

Public Function ClearCellsBelowValuesRange()
Dim Rng As Excel.Range, offset As Integer
    Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("ValuesRange")
    offset = 1
    Do
        Rng.offset(offset, 0).Value = ""
        offset = offset + 1
    Loop Until IsEmpty(Rng.offset(offset, 0).Value)
End Function