Error in Range(...).ClearContents

[LittleBear]

Hi, everybody!

I've been using for many years the same structure to clear data from tables without any issues, even in other worksheets of the same workbook.

I defined a name called TableVideos as:

=OFFSET(VideoList!$A$1,,,COUNTA(VideoList!$D:$D),COUNTA(VideoList!$1:$1))

The first 20+ lines of worksheet VideoList have data in all cells of column D.

In a VBA procedure I have this code, among a bunch of unrelated other lines.

const gksVidLstWS="VideoList"
const gksVidLstRng="TableVideos"

Dim rngVideos as Range
Set rngVideos = Worksheets(gksVidLstWS).Range(gksVidLstRng)

    With rngVideos
        ' clear
        If .Rows.Count > 1 Then
            Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
        End If

.... and the code goes on and compiles fine.

When the execution reaches the ClearContent line, it raises a 1004 error, which is weird since the same structure in other worksheets of same workbook (and in a thousand files!) works perfectly.

I tried many things in the Immediate window:
? rngVideos.Rows.Count -----> 24
? rngVideos.cells(1,1).value -----> displays correctly header of column 1.
? rngVideos.cells(24,15).value -----> same for last cell of last column of last row (O24)
? worksheets("VideoList").range("TableVideos").address -----> $A$1:$O$24
? Range(.Rows(2), .Rows(.Rows.Count)).Address -----> ... and either raises 1004 error.

Can you help me, please? I have a good expertise in VBA but I'm astonished and clueless with this error.

I'm attaching the workbook, to reproduce the error, in worksheet Parameters click Validate button, then when Execute button gets visible click on it and... kaboom.

Thank you very much in advance for the attention given to this matter.

Best regards.

Paul.

 

[vletm]

below line's range refers to 'activesheet'

Range(.Rows(2), .Rows(.Rows.Count)).ClearContents

below line's range refers to ... rngVideos

.Range(.Rows(2), .Rows(.Rows.Count)).ClearContents

The dot can make a lot.

 

[Debaser]

Since the code is in a worksheet code module, using Range with no worksheet qualifier refers to that worksheet, not the active one or the one the range is actually on.

 

[LittleBear]

below line's range refers to 'activesheet'

Range(.Rows(2), .Rows(.Rows.Count)).ClearContents

below line's range refers to ... rngVideos

.Range(.Rows(2), .Rows(.Rows.Count)).ClearContents

The dot can make a lot.

It worked like a charm. Just in front of my eyes, so close that I did't see it.
Well deserved the Ninja badge, Vletm. Chapeau , monsieur.

 

[LittleBear]

BTW, I'm using Office Professional Plus 2021, it this a new behaviour of VBA environment? Why does the Find/Replace window insist on getting sticked at top left of the VB IDE window instead of remaining where I place it manually?