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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,257
Messages
5,657,635
Members
418,404
Latest member
Sneijders

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
Top