Reassign Range and Looping

Jon Melone

Board Regular
Joined
Mar 10, 2008
Messages
109
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
You should find that this will do what you need, it's based on finding the row after a blank cell:
Code:
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

Board Regular
Joined
Mar 10, 2008
Messages
109
Thanks for providing an interesting solution - well, at least interesting to me since I'm just a beginner!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,939
Messages
5,508,250
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top