Reassign Range and Looping

[Jon Melone]

Hi,
I need to help to loop through a worksheet while highlighting a range of cells. Once I have my first range defined, I'm unable to figure out how to move the range to the next appropriate row. The next row is not constant, but can be determined by .End(xlDown).End(xlDown).

'code snapshot starts here
Dim totalLoop As Integer 'number of items with a forecast
Dim fcstHighlight As Range 'highlight forecast values
Dim highlightCounter As Byte 'control highlight of forecast row looping

'assign initial values
totalLoop = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
Set fcstHighlight = Range(Range("C2"), Range("C2").End(xlToRight))
highlightCounter = 1

'run through loop
Do While totalLoop >= highlightCounter
With fcstHighlight.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Set fcstHighlight = ??? 'move to next appropriate row
highlightCounter = highlightCounter + 1
Loop
'end of code snapshot

My goal is to move the range down the spreadsheet based on .End(xlDown).End(xlDown) as there are rows with data to pass-over and then blank rows to skip. The first row after the blank rows is the next fcst row.

My attempts have either created a larger range by starting with the original range and including everything between the last row or only moving to the first cell within the targeted range, but not assigning the full range across the columns.

Jon

 

[Simon Lloyd]

You should find that this will do what you need, it's based on finding the row after a blank cell:

Sub Find_Next_Area()
'Simon Lloyd http://www.thecodecage.com
'code snapshot starts here
Dim fcstHighlight As Range 'highlight forecast values
Dim NwRng As String, EndAddress As String

'assign initial values
Columns(1).Find(What:="", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
EndAddress = Range("A" & Rows.Count).End(xlUp).Address
Set fcstHighlight = Range(Range("C2"), Range("C2").End(xlToRight))

'run through loop
Do

Columns(1).FindNext(After:=ActiveCell).Activate
With fcstHighlight.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
NwRng = Range(Range("C" & ActiveCell.Row), Range("C" & ActiveCell.Row).End(xlToRight)).Address
Set fcstHighlight = Range(NwRng).Offset(1, 0)

Loop Until ActiveCell.Address > EndAddress
'end of code snapshot

End Sub

 

[Jon Melone]

Thanks for providing an interesting solution - well, at least interesting to me since I'm just a beginner!