Clear Dynamic Range

[ionelz]

Hello,
I would like a simple code for Clear Content of Range
Dynamic Value is in cell C3
Dynamic Columns are F,G,H,I,J,K,L,M,N,O,P,Q so 12 Columns
If C3=3 then Clear Range I4:Q6
If C3=10 then Clear Range P4:Q6
If C3=6 then Clear Range L4:Q6
Basic, Clear Content of end range
Thank you

 

[maxpit]

Hi ionelz,

you can try this code

Sub Test()

    Dim Rif As Long
    Dim rRng As Range
 
    ' check the contents of cell "C3"
    With Range("C3")
        If Len(.Text) Then
            If IsNumeric(.Value) Then
                Rif = .Value
            End If
        End If
    End With
 
    If Rif = 3 Then
        Set rRng = Range("I4:Q6")
    ElseIf Rif = 10 Then
        Set rRng = Range("P4:Q6")
    ElseIf Rif = 6 Then
        Set rRng = Range("L4:Q6")
    End If

    If Not rRng Is Nothing Then
        rRng.ClearContents
    End If

End Sub

Let me know if this is what you need.

 

[ionelz]

sorry, no, I just give some example
I need to offset any C3 value end clear
Whole range is F4:Q6
So if like C3=3 then skip F,G,H and clear I4:Q6 or if example C3=5 then skip F,G,H,I,J and clear K4:Q6

 

[maxpit]

Hi ionelz,

Before proceeding, can you provide me with the following information:

1. Values to delete ranges, can I also read them from cells other than "C3"?
2. If the cell content (e.g. "C3") is 0: should the entire range from F4:Q6 be deleted?

Thanks in advance

 

[ionelz]

C3 range from 1 to 12, only C3 is used

 

[ionelz]

C3 range from 1 to 12, only C3 is used

So what I want is Clear Content from F4+Value C3 to Q6
And how to insert this in my Sheet2 so it update when change at C3

 

[NoSparks]

sorry, no, I just give some example
I need to offset any C3 value end clear
Whole range is F4:Q6
So if like C3=3 then skip F,G,H and clear I4:Q6 or if example C3=5 then skip F,G,H,I,J and clear K4:Q6

So if C3 = 12 then skip everything ??

 

[Cubist]

Try:

Sub ClearRange()
    Range("F4").Offset(0, Range("C3").Value).Resize(3, 12 - Range("C3").Value).ClearContents
End Sub

 

[maxpit]

Hi ionelz,

let me know if the code below meets your requirements

Sub Test1()

    Dim Rif As Long
    
    ' check the contents of cell "C3"
    Rif = -1
    With Range("C3")
        If Len(.Text) Then
            If IsNumeric(.Value) Then
                Rif = .Value
            End If
        End If
    End With
    If Rif > -1 And Rif < 11 Then
        Range(Cells(4, 6 + Rif), Cells(6, 17)).ClearContents
    End If

End Sub

Bye

 

[ionelz]

I have copy the code in my Sheet but when I change at C3 it doesn't update
Saved file as xlsb but still code do not work