Firing ClearContents code misbehaves when run twice

[detriez]

I'm trying to get this to clear the contents of everything except Row 1 when it is populated

This works when only run once. But, running it a second time will clear the contents of row 1 as well

I tried adding a +1 to the LastRow line but that didn't matter

Dim thiswksht    As Worksheet
    Dim LastRow   As Long
    Dim LastCol   As Integer
    Dim rngRange       As Range
    Set thiswksht = ActiveSheet
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    Set rngRange = Range("A2", Cells(LastRow, LastCol))
    rngRange.name = "tbl_P"

        With rngRange
        End With

 Selection.ClearContents
 
    Range("A2:BI2").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

 

[Fluff]

The only thing that code will clear is whatever cell(s) you had selected before running the macro.

 

[detriez]

Thanks Fluff.
I thought this part was finding the last populated column and row then setting the range to select and then clear.

    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    Set rngRange = Range("A2", Cells(LastRow, LastCol))
    rngRange.name = "tbl_P"

 

[Fluff]

No that's just creating a defined name for the range, which seems a bit odd if you want to clear it.

 

[detriez]

I am using the xlsm file as a template. On occasion I need to clear the file and start over.
Here is the the full Function (I should have posted this with my original post)

Sub ClearAndNew(control As IRibbonControl)

Dim thiswksht    As Worksheet
    Dim LastRow   As Long
    Dim LastCol   As Integer
    Dim rngRange       As Range
    Set thiswksht = ActiveSheet
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    Set rngRange = Range("A2", Cells(LastRow, LastCol))
    rngRange.name = "tbl_P"

        With rngRange
        rngRange.Select

        End With

 Selection.ClearContents
 
    Range("A2:BI2").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With


End Sub

 

[Fluff]

Try it like

Sub ClearAndNew(control As IRibbonControl)

Dim thiswksht    As Worksheet
    Dim LastRow   As Long
    Dim LastCol   As Integer
    Dim rngRange       As Range
    Set thiswksht = ActiveSheet
    With thiswksht
        LastRow = .Cells(Cells.Rows.Count, "B").End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rngRange = .Range("A2", .Cells(LastRow, LastCol))
    End With
    rngRange.Name = "tbl_P"

    If LastRow > 1 Then rngRange.ClearContents
 
    Range("A2:BI2").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With


End Sub

 

[detriez]

That works.
I think I was trying to manage this too soon in the code.. Makes more sense to do it when you did.

Thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback.