Range of row VBA Excel-CodePudding

I want to create a code, in VBA, to clear content. I can't use range because it's not continue I want to create a range with variable (row number). I have row 25, 65, 67, 69, 71, 77, 79... Nothing is logical in this suite.

Range("J25").MergeArea.ClearContents
Range("J65").MergeArea.ClearContents
Range("J67").MergeArea.ClearContents
Range("J69").MergeArea.ClearContents
Range("J71").MergeArea.ClearContents
Range("J77").MergeArea.ClearContents
Range("J79").MergeArea.ClearContents
Range("J81").MergeArea.ClearContents
Range("J83").MergeArea.ClearContents

I try something like

Dim k as Byte
for k =    to   
Range("D" & K).mergeArea.clearContents
Next K

But it's doesn't work

Thank for help

CodePudding user response：

One way to do it

Option Explicit

Sub test()

Dim RowVal As Variant
Dim i As Long

RowVal = Array(1, 3, 6) 'Store the rows in an array
For i = LBound(RowVal) To UBound(RowVal) 'loop through the array. From the first value (lower bound) to the last value (upper bound)
    Range("A" & RowVal(i)).MergeArea.ClearContents 'refer to the array when we use the range value
Next i

End Sub

CodePudding user response：

Referencing Merged Cells

Option Explicit

Sub RefCombinedMergedCellsTEST()
' Needs 'RefCombinedMergedCells' and 'RefCombinedRange'
    
    Const CellsList As String = "J25,J65,J67,J69,J71,J77,J79,J81,J83"
    Dim mcrg As Range: Set mcrg = RefCombinedMergedCells(Sheet1, CellsList)
    If mcrg Is Nothing Then Exit Sub
    With mcrg
        .ClearContents
        'Debug.Print .Address(0, 0)
        '.Interior.Color = vbYellow
        '.Font.Bold = True
    End With
    
End Sub

Function RefCombinedMergedCells( _
    ByVal ws As Worksheet, _
    ByVal CellAddressesList As String, _
    Optional ByVal Delimiter As String = ",") _
As Range
' Needs 'RefCombinedRange'

    Dim cAddr() As String: cAddr = Split(CellAddressesList, Delimiter)
    
    Dim mcrg As Range
    Dim mcCell As Range
    Dim n As Long
    
    For n = 0 To UBound(cAddr)
        If ws.Range(cAddr(n)).MergeCells Then ' merged cells
            Set mcrg = RefCombinedRange(mcrg, ws.Range(cAddr(n)).MergeArea)
        Else ' not merged cells
            Set mcrg = RefCombinedRange(mcrg, ws.Range(cAddr(n)))
        End If
    Next n
    
    If mcrg Is Nothing Then Exit Function
    
    Set RefCombinedMergedCells = mcrg

End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Creates a reference to a range combined from two ranges.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefCombinedRange( _
    ByVal CombinedRange As Range, _
    ByVal AddRange As Range) _
As Range
    If CombinedRange Is Nothing Then
        Set RefCombinedRange = AddRange
    Else
        Set RefCombinedRange = Union(CombinedRange, AddRange)
    End If
End Function