How to delete values of a range in Excel VBA

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!

Answers


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

If you want just to delete values you can try:

startRange.Select
Selection.ClearContents

Need Your Help

How Swift implement Array's copy-on-write behavior?

arrays swift memory wwdc

After watching build better apps with value type . In the photoshop example they made, they said that

Scanner.nextLine(); called before wanted due to loop

java loops

I have a scanner input which is called by getName() in my Player class.