Q: how to use UsedRange correctly?

[alex]

Hi There,

I'm using UsedRange in a couple of places and hoping to get advice on
what I'm doing incorrectly. The data that's in the worksheet is from
andexternal data source so it changes.
What I'm seeing is that the number of columns that UsedRange see's is
correct but that the number of Rows is not. If it wasn't for the "If
somecount - 400 then Exit For" the macro would just run forever
(requiring me to KILL excel and loose any unsaved changes. D'oh)

Also, If I do cntrl-end, i end up at the botton right corner of
IV399...399 being where I for that stop.

Why aren't my results meeting my expectations?

Many Thanks,
Alex


UsedRange.Interior.ColorIndex = 2
and
For Each CurrCell In ActiveSheet.UsedRange.Range("AG:AG")
somecounter = somecounter + 1
If CurrCell >= 119 Then
'Range("A" & CurrCell.Row & "" &
CurrCell.Row).Interior.ColorIndex = 6
'Range("A" & CurrCell.Row & ":" & NumOfColumns & _
CurrCell.Row).Interior.ColorIndex = 6
CurrCell.EntireRow.Interior.ColorIndex = 6
End If
CurrCell.EntireRow.Font.ColorIndex = 1
If somecounter = 400 Then
Exit For
End If
Next

 

[Guest]

Option Explicit

Sub checker()
Dim ws As Worksheet
Dim CurrCell As Range
Dim lastrow As Long
Set ws = ActiveSheet

With ws.UsedRange
lastrow = .Row + .Rows.Count - 1
End With

ws.UsedRange.Interior.ColorIndex = 2

With ws
For Each CurrCell In .Range(.Range("AG1"), .Cells(lastrow, "AG"))
If CurrCell.Value >= 119 Then
CurrCell.EntireRow.Interior.ColorIndex = 6
End If
CurrCell.EntireRow.Font.ColorIndex = 1

Next
End With
End Sub

 

[Norman Jones]

Hi Alex,

See Debra Dalgleish's notes on resetting the used range:

http://www.contextures.com/xlfaqApp.html#Unused

 

[alex]

Thanks for the great replies. I've learned a lot from that example and
the FAQ (though I also have a bunch more questions too

Cheers!
Alex