Firing ClearContents code misbehaves when run twice

detriez

Board Regular
Joined
Sep 13, 2011
Messages
162
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
The only thing that code will clear is whatever cell(s) you had selected before running the macro.
 

detriez

Board Regular
Joined
Sep 13, 2011
Messages
162
Office Version
  1. 365
Platform
  1. Windows
Thanks Fluff.
I thought this part was finding the last populated column and row then setting the range to select and then clear.

VBA Code:
    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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
No that's just creating a defined name for the range, which seems a bit odd if you want to clear it.
 

detriez

Board Regular
Joined
Sep 13, 2011
Messages
162
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
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
 
Solution

detriez

Board Regular
Joined
Sep 13, 2011
Messages
162
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,644
Messages
5,654,552
Members
418,140
Latest member
ahepple86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top