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!
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: