More Range Problems

[AlexanderBB]

I'm sure Excel is out to get me, with random VBA bugs that suddenly turn up.
This last one was rather nasty and took hours to track down.

I'm repeating this line many times populating rows. I get from 843 to 860

    With Worksheets("1990+")
        Set cc = .Range(.Range("Y" & irow), .Range("CQ" & irow).End(xlToLeft))
end with

I use cc to change some font colours. All is ok for a while then the colours go nuts.
The reason is cc.address.
Instead of something like $Y$851:$CP$851 it is $Q$860:$Y$860
I can prove this by stopping in the code and printing in the debug window
Set cc = .Range(.Range("Y851"), .Range("CQ851").End(xlToLeft))
? cc.address
$Y$851:$CP$851
Set cc = .Range(.Range("Y" & irow), .Range("CQ" & irow).End(xlToLeft)) [irow is 860]
? cc.Address
$Q$860:$Y$860
This can be repeated at will. But there is nothing (obvious) about line 860 to cause this.
Can anything suggest a reason, or some debugging to try ?

 

[MARK858]

First of all you are having an issue with irow but you haven't posted the code that you are using to define irow

 

[StephenCrump]

Your code is finding the last populated cell left of Range("CQ" & irow).

You are assuming this will be in column Y or greater, but in row 860 it's actually column Q.

It looks like your code will need to test for this last column, and colour cells only if it is equal to column Y or greater?

(For context: Last Col in Range although it's a bit TLDR)

 

[MARK858]

Same issue in this thread?

Range with no values

I can't find the exact thread here where I was given Set cr = .Range(.Range("Y" & Row), .Range("CQ" & Row).End(xlToLeft)) This catches all values in row from Y to CP, ignoring blanks. I've been using it and getting perfect results until a situation where nothing was in that row. Say it's row...

www.mrexcel.com

 

[AlexanderBB]

It wasn't really an assumption as each row is newly inserted then populated, and the last used cell varies. But always starts at Y and Y is always something.
The problem was not there for rows 843-859 which repeats the same procedure.
I've just run your Replace Fix Stephen, and that fixes it again
? cc.Address
$Y$860:$Z$860 = Correct
Although that had been run on the entire sheet (took about 15 mins) it again errored, so I ran the Fix for every row.
Looks like I need it every time Set cc = .Range(.Range("Y" & irow), .Range("CQ" & irow).End(xlToLeft)) is used.
Or perhaps do it some other way? I though using Excels own methods would be the way to go.
I bet you'll be thinking I'm putting some oddball thing into that Range, but I'm sure I'm not.
This code may be meaningless but just to show the structure. All is Okay up to the first Stop

   Do
        eRow = eRow + 1
If eRow = 860 Then Stop
        Rows(eRow).EntireRow.Insert
        DoEvents
            For i = 1 To 24
                If i = 2 Then i = 3
                Set iRange = Range(colLetter(i) & eRow)
                Set Acc = New clsAccess
                aCol = Acc.AccessIndex(iRange) ' This is the Access index for Excels column
                Cells(eRow, i) = rx(aCol)
            Next i

            For i = 95 To 100
                Set iRange = Range(colLetter(i) & eRow)
                Set Acc = New clsAccess
                aCol = Acc.AccessIndex(iRange)
                Cells(eRow, i) = rx(aCol)
            Next i

 Stop
'rx(27) is a csv string           
PopCR eRow, rx(27)

Sub PopCR(irow, AccessCR)

    Dim Dat As Variant
    Dim i As Integer
    Dat = Split("," & AccessCR, ",")
    With Worksheets("1990+")
        For i = 25 To 94
            If (i - 24) <= UBound(Dat) Then
            .Cells(irow, i) = Dat(i - 24)
            Else
            .Cells(irow, i) = ""
            End If
        Next
        
    End With
    Stop
    Color1990ChartRun irow
End Sub

And this is where the problem shows, first (so far only) on row 860

Sub Color1990ChartRun(irow)

    Dim cc As Range
    Dim sa As String
    Dim i As Integer
    With Worksheets("1990+")
    
    If irow = 860 Then
     With Worksheets("1990+")
        With Intersect(.UsedRange, .Range("Y860:CP860"))
            'xxx is something that won't be found in the range.  Change if necessary!
            .Replace What:="", Replacement:="xxx", LookAt:=xlWhole, MatchCase:=False
            .Replace What:="xxx", Replacement:="", LookAt:=xlWhole, MatchCase:=False
        End With
    End With
    End If
    
        Set cc = .Range(.Range("Y" & irow), .Range("CQ" & irow).End(xlToLeft))
        Stop

 

[Alex Blakenburg]

If you know your cell is going to be empty before you run your code you don't need these lines:
Note: Populating a Text formatted cell with "" is registering as not being blank/empty

            Else
            .Cells(irow, i) = ""

If your are not sure either of these should work:

            Else
            .Cells(irow, i).ClearContents

            ' OR
            Else
            .Cells(irow, i) = Empty

 

[AlexanderBB]

Thanks Alex, I'll remove the "". You're quite right, they must be empty at that point.
Apart from the 'thing' that buggers it up.
Perhaps it'll change something...... it may not need anything done with the trailing unused cells, but I did it that way to also work
with a longer to shorter set of numbers

 

[Alex Blakenburg]

If you make that change then run @StephenCrump's clean up code, hopefully you won't have the issue going forward ie when you refresh the data.

 

[AlexanderBB]

Sure. But a newly-inserted row with Y-CP = "". Then replace "" with "xxx" and then "xxx" with "".
Yes it fixes the problem but how/why does that work??