CODEWITHSUNDEEP
excelvba
TurboCoder
TurboCoder

Reputation: 1011

VBA - Clear Cells of Found (Dynamic) Range Up to Last Row

This is pretty much the same question posted here:

Excel VBA - How to clear a dynamic range (from below header, to last row)

However, given that this is an old thread AND the solutions provided are not working, I am posting the question in the way I am trying to solve my problem.

Two solutions found here for finding correct column:

VBA - Find a column with a specific header and find sum of all the rows in that column

https://www.extendoffice.com/documents/excel/4879-excel-select-column-by-header-name.html

Solution for Clearing Contents found here:

Excel VBA - How to clear a dynamic range (from below header, to last row)

Here is what I have tried to make work:

With ActiveSheet

    xStr = "Product Folder"

    Set aCell = .Range("B2:J2").Find(What:=xStr, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

    'If Found
    If Not aCell Is Nothing Then

        col = aCell.Column
        colName = Split(.Cells(, col).Address, "$")(1)

        lRow = .Range(colName & .Rows.count).End(xlUp).Row

        'This is your range
        Set Rng = .Range(colName & "2" & lRow)

    'If not found
    Else

        MsgBox "Column Not Found"

    End If

    Range(Rng).Resize(Rows.count - 1, Columns.count).Offset(1, 0).ClearContents

End With

I can get ALL the information required (Column, Header, Range ect) but I cannot seem to find a way of clearing the contents.

With the code as is above, I get the error:

Method 'Range' of object '_Global' failed

I have tried changing 

    lRow = .Range(colName & .Rows.count).End(xlUp).Row

    'This is your range
    Set Rng = .Range(colName & "2" & lRow)

To this:

    lRow = Range(colName & .Rows.count).End(xlUp).Row

    'This is your range
    Set Rng = Range(colName & "2" & lRow)

And this line:

Range(Rng).Resize(Rows.count - 1, Columns.count).Offset(1, 0).ClearContents

To this:

Rng.Resize(Rows.count - 1, Columns.count).Offset(1, 0).ClearContents

But then I get this error:

Application-defined or object-defined error

Tried changing this:

    'If Found
If Not aCell Is Nothing Then

    col = aCell.Column
    colName = Split(.Cells(, col).Address, "$")(1)

    lRow = .Range(colName & .Rows.count).End(xlUp).Row

    'This is your range
    Set Rng = .Range(colName & "2" & lRow)

'If not found
Else

    MsgBox "Column Not Found"

End If

Range(Rng).Resize(Rows.count - 1, Columns.count).Offset(1, 0).ClearContents

To this:

'If Found
    If Not aCell Is Nothing Then

        col = aCell.Column
        colName = Split(.Cells(, col).Address, "$")(1)

        lRow = Range(colName & .Rows.count).End(xlUp).Row + 1

        Set myCol = Range(colName & "2")

        'This is your range
        Set Rng = Range(myCol & ":" & colName & lRow)

    'If not found
    Else

        MsgBox "Column Not Found"

    End If

    Rng.Clear

Then I get an error at the Set Rng line:

Method 'Range' of object '_Global' failed

I know the line itself isn't really correct (obviously) but I just don't know how else to get the code to read it as Range("A2:A" & Lastrow).Clear

Upvotes: 1

Views: 1474

Answers (1)

Vityata
Vityata

Reputation: 43575

This is indeed what causes the error:

Set Rng = Range(myCol & ":" & colName & lRow)

The Range() requires parameters that are either Ranges or Strings.

Not both. Try this: Set Rng = Range(myCol.Address & ":" & colName & lRow)

Sub TestMe()

    Dim rng As Range
    Dim myCol As Range
    Dim colName As String
    Dim lRow As Long

    Set myCol = Range("A5")
    colName = "A"
    lRow = 15
    Set rng = Range(myCol.Address & ":" & colName & lRow)    
End Sub

Or try this, with passing 2 range objects:

With Worksheets(1)
    Set rng = .Range(myCol, .Cells(lRow, colName))
End With

Upvotes: 1

Related Questions