ActiveSheet.UsedRange missing cells

orangehenry

New Member
Joined
Oct 26, 2015
Messages
7
Hi,
I have a vba script that is clearing the contents of all cells that meet a certain formatting condition (ColourIndex). It does this by cycling through all worksheets and looking at the ActiveSheet.UsedRange
However, I am finding that this is missing random cells in my worksheets. I understand that UsedRange can be a bit buggy but I'm unclear how to integrate some of the workarounds I've seen on the forum into my script.

Can anyone suggest the fix so that my script won't miss cells in my workbook?

SQL:
Private Sub CommandButton1_Click()
'Updateby Extendoffice
    Dim rngCl As range
    Dim xRows As Long
    Dim xCol As Long
    Dim colorLg As Long
    Dim ws As Worksheet, a As range
        
    colorLg = 40
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    With ActiveSheet.UsedRange
        For xRows = .Rows.Count To 1 Step -1
            For xCol = 1 To .Columns.Count
                           If .Cells(xRows, xCol).Interior.ColorIndex = colorLg Then
                    .Cells(xRows, xCol).ClearContents
                    Exit For
                End If
            Next xCol
        Next xRows
    End With
    Next
    Sheets("Detailed Stock Schedule").range("A4:C400").ClearContents
    Sheets("Detailed Stock Schedule").range("E4:J400").ClearContents
    Sheets("Detailed Stock Schedule").range("L4:O400").ClearContents
    Application.ScreenUpdating = True
    Unload UserForm2
End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
See if this works.

Dim cell As Range

For each cell in ActiveSheet.Usedrange
do your thing
Next cell
 
Upvote 0
See if this works.

Dim cell As Range

For each cell in ActiveSheet.Usedrange
do your thing
Next cell
Thanks - but this killed Excel. Seemed like it got stuck in a loop.

I tried this as

SQL:
dim cell as range
For each cell in activesheet.usedrange
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    With ActiveSheet.UsedRange
.....

Is that the right spot to enter it? I feel like I might need to remove some of the existing steps i had.
 
Upvote 0
Right spot but you don't need the "With ActiveSheet.UsedRange" statement.

I'm on cellphone. So, it's not easy to type long messages.
 
Upvote 0
Oh! Now that I have looked at the code more closely, "For each cell..." should be below "For each ws...." and "ws.Activate".
 
Upvote 0
Getting closer. I tried limiting the number of sheets that it would cycle through to see this was there error but its still putting excel into an indefinite loop.

Here it is again in full. I feel like its not accurately picking up what the UsedRange is. Is there a way to print/see what UsedRange is?

VBA Code:
Private Sub CommandButton1_Click()
'Updateby Extendoffice
    Dim rngCl As range
    Dim xRows As Long
    Dim xCol As Long
    Dim colorLg As Long
    Dim ws As Worksheet, a As range
    Dim cell As range
            
    colorLg = 40
    Application.ScreenUpdating = False
    For Each ws In Sheets
        Select Case ws.Name
        Case Is = "Inputs", "Funding Table", "Detailed Stock Schedule"
    ws.Activate
    For Each cell In ActiveSheet.UsedRange
    'With ActiveSheet.UsedRange
        For xRows = .Rows.Count To 1 Step -1
            For xCol = 1 To .Columns.Count
                           If .Cells(xRows, xCol).Interior.ColorIndex = colorLg Then
                    .Cells(xRows, xCol).ClearContents
                    Exit For
                End If
            Next xCol
        Next xRows
    'End With
     Next cell
    End Select
       
    Next ws

    
            
    Sheets("Detailed Stock Schedule").range("A4:C400").ClearContents
    Sheets("Detailed Stock Schedule").range("E4:J400").ClearContents
    Sheets("Detailed Stock Schedule").range("L4:O400").ClearContents
    Application.ScreenUpdating = True
    Unload UserForm2
End Sub
 
Upvote 0
Your Exit For statement means that it will only ever clear one cell per row - perhaps that is the problem?

I've never seen Usedrange underestimate the correct range. FWIW, you don't need to activate the sheets.
 
Upvote 0
100%
Your Exit For statement means that it will only ever clear one cell per row - perhaps that is the problem?

oh now that you've said it that is 100% what its doing. I just hadn't picked up on it. This code is bastardised off something that was looping to delete rows, so thats not entirely suprising.

So how do I amend the way it cycling through cells to stop that behaviour?
 
Upvote 0
Just remove the Exit For line. :)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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
Back
Top