Used Range in Vba taking all cells not just used cells

[dsrt16]

I have a code in vba to clear all unlocked cells in the used range on every sheet.

It takes 40 minutes to execute this code. In order to figure out why it was taking so long, I used vba to select the used range.

I do not use any columns past column S. But currently, it has all the way to column iv as part of the used range. Same thing with the rows. It selects far more than the actual used rows. A user will never go past column S, but as users add new rows to tables, it is hard to determine how many rows they will use.

Anyway, before building my excel tables, I first selected all cells and changed the fill to white (thus getting rid of the excel gray lines). Since I did that, I think that is why it is counting it as part of the used range.

Is there any way to get this code to just check the actual used range. The cells with data in them not just formatting?

Sub NewYear()
Dim cell As Range, Sht As Worksheet


For Each Sht In ThisWorkbook.Worksheets
   If Sht.Name <> "Setup Page" And Sht.Name <> "Lists" Then
    For Each cell In Sht.UsedRange
    If cell.Locked = False Then cell.Value = ""
    Next cell
    End If
 Next Sht

 Call UnprotectRefresh

End Sub

 

[Fluff]

The best way would be to delete all the columns from T to IV. Remove the background, & turn grid lines off.
Then save & close your workbook. Once you re-open it your macro should work.

 

[sheetspread]

The Find method and other alternatives to .usedrange:

https://stackoverflow.com/questions/7423022/getting-the-actual-usedrange

 

[Rick Rothstein]

The best way would be to delete all the columns from T to IV. Remove the background, & turn grid lines off.
Then save & close your workbook. Once you re-open it your macro should work.

Maybe reset the remaining cells' fill color to xlNone as well? I see no reason to keep the white fill color once the grid lines are turned off.

 

[Fluff]

Maybe reset the remaining cells' fill color to xlNone as well? I see no reason to keep the white fill color once the grid lines are turned off.

That's what I meant. Maybe I should have said - Remove the background fill from the entire sheet.

 

[Rick Rothstein]

That's what I meant. Maybe I should have said - Remove the background fill from the entire sheet.

My fault... I read your response to quickly.

 

[dsrt16]

All this time, and I never knew you could turn the gridlines off. Mind blown! Haha.

Ok thanks. Easy fix!

 

[Fluff]

Glad we could help & thanks for the feedback